Excel 2003 - VBA
A quick question on the "Workbook Open" event: When I load up Excel and open
the workbook, this event triggers. If I then close the workbook, without
closing Excel, then open the workbook, it does not occur. Close down the
worksheet and Excel, then open it, it triggers.
Is this normal operation?
Could it have to do with the possibility the I had set
Application.EnableEvents = False, before exiting the workbook.
Also put in
Microsoft MVP Excel
"Craig Brandt" <brandtcraig...Worksheet properties
For some strange reason....all new worksheets that I
insert into a workbook seem to right-to-left
orientation. So the A column is located on the right
hand side and the b column is located to the left of the
a column. Does anyone know if this is a setting from the
menu or an object that needs resetting in vb?
Look at Tools - Options - International - Default direction. I think this
is only for a new workbook. Doesn't change existing ones.
mvpearl omitthisword at verizon period net
"Matt...Lock and Unlock cells using VBA
Anyone know how to lock cells using VBA, also unlock others currently locked.
Page protection will be in use at the time this needs to be done.
Also, is there anywhere on the net with a complete list of VBA functions,
including a short statement of what each one does, with or without examples.
If not, any good books that cover the above area. I'm really (strange as it
might seem), enjoying playing with VBA and seeing exactly what it can do.
The easy questions...
Excel's Help is a very good source.
take a look at Peter Nonely's workbook that describes...Printing all worksheet names
Does anyone know if it possible to print a list of
worksheet names? A list similar to the one you would find
in the "Contents" tab of the files' Properties.
Thanks for any help you can provide.
Programme - XP Excel
This topic was covered lately in microsoft.excel.worksheet.functions NG
(thread 'print sheet tab names' started by JDB at 06.01.2004 16:00). Here is
the solution from me:
Create an UDF
Public Function TabByIndex(TabIndex As Integer) As String
TabByIndex = Sheets(TabIndex).Name
On some empty worksheet,...Copy Protection Device
No matter what computer I connected, the RMS always pops-up a window with
the following message whenever I try to open the Store Operations Manager
from a different station:
Warning 941: The copy protection device that used to be on this register was
not found. Do you wish to continue?
We have a server, 3 pc connected to it, one is used for the Store Operations
POS (I have the parallel dongle on this one), the other 2 are connected for
the Store Operations Manager.
The system is not in evaluation mode anymore, but it is a little bit annoying.
You need to create a...Writting Function using VBA
I am trying to write a function to return an address but
instead I get #VALUE!.
Public Function fnd(a, b)
fnd = Range(a).Find(b).Address
..Find won't work in UDF's called from the worksheet until xl2002.
Depending on the range (a), you could use application.match() through each
If the range is small, you could just loop through the values in that range,
> I am trying to write a function to return an address but
> instead I get #VALUE!.
> Public Function fnd(a, b)
>...Populate List Box
Need some ideas.
I have a list box from which our clients will be able to select which form
they want to preview or print. However, each client should only have
selections available that apply specifically to their company. For example:
Company A will require forms 1, 2, 3, 6 and 7
Company B will require forms 1, 6 and 7
Company C will require forms 4, 5 and 6
I have some code right now to handle a simple two form variation:
If Forms("Main").Controls("Form1Req") = "Yes" and Forms("Main").Controls
("Form2Req") = "No" Then
Me.L...Link Rows Between Worksheets
I am trying to link rows (that are infinitely long in terms of data) between
two different worksheets so that I dont have to retype all of the data. (I
want them to be active links, so that if the data changes, it will
automatically update). Is there a way to do this?
(The first worksheet is a master that has a whole lot of information, and I
would like the other worksheets to reference rows from the original...)
Thank you very very much..
In the other sheets you will have to set then up
eg: in sheet2 select the cell you want then type an equals sign =
got to master sheet and se...Sheet protection with unlocked cells
I just upgraded from XP to 2003. I have an XP spreadsheet
that is protected except for a number of cells that are
open for anyone to enter data in. They use a macro to
clean all prior data before they beginn their entery. 1)
Why does the macro (when run on my 2003 software) lock all
cells? 2) How do I remove data without changing ANY cell
you may post the existing macro
> I just upgraded from XP to 2003. I have an XP spreadsheet
> that is protected except for a number of cells that are
>...Carrying From Worksheet to Worksheet
In Excel 2002 (10.4524.4219) SP-2, I have designed a worksheet based
on IRS form Schedule D-1 to report capital gains. Because I have more
than one sheet in the file, I would like to devise a formula that
carries a total from one sheet to the next. Specifically, I would like
cell F34 on any given sheet to be a running total of the figures in
cells F8 through F31 on that sheet (which is easy enough to
accomplish) PLUS (and here's where I'm stuck) the totals of cells F8
through F31 on all the previous sheets (or, the same thing said
differently, plus the figure in cell F34 on every previ...Cell extraction from Multiple worksheets
I am new to this newsgroup and am hoping that you can help me out. I took a
software/hardware inventory of all the computers in my work's network and
imported them all into excel, in their own worksheet. So I have one file,
with 128 different worksheets, all containing similar information.
What I would like to do is create a new sheet, a summary page, where I can
pull the value of the same cell from each sheet and list them. I need to
have a list of each computer, with computer name, cpu speed, video card,
serial number, ect. If the values that I am searching for are in the same...Sorting problems with other worksheets!!!
I have a problem!!!
First I have multiple worksheets in a workbook.
And in one column that has some names. and the columns next to them has data
which is yes/no go/ngo for each person.
If I add a person it will update itself in each worksheet. But I need to
So I sort it and it sorts fine in the worksheet I'm working in. But with
the other worksheets it sorts it but the data for each person doesn't stay
with the person...
I hope I explained this problem to where someone can understand. PLEASE
It used to happen when a column (in this example the one containing ...Email attempt bungled worksheet
OK, here's the scenerio. I decided to email my company timesheet to my home address so I could have it at home in case I could not make it in. So, from the File menu, I selected Send To Mail Recpient. The worksheet has a protected cell somewhere, so i got an error saying I would have to unprotect it to send it. No biggie. Now he's the aggravating part. Whenever I open my timesheet now it has the outlook tool bar, for lack of a better phase, stuck in there. It's quite annoying. I can't for the life of me find a way to get rid of it. I wish I could copy a screen shot, it the To:,...Password Cell Protection
I'm seeking to protect specific cells within a given worksheet. I've been
reading some of the newer posts on the topic, and see that I'm to first go
in, unlock cells that I want accessible, and then once all cells are
configured- locked or unlocked state- I go to the Review Tab, select Protect
Sheet, choose the options I want users to have access to, and select/enter
the desired password- twice.
Once I do this, all appears to be as desired, until I try to insert or
delete a row-- something I'd specifically allowed for on my unlocked
...Printing landscape worksheets with footer on left
I know that I must not be the first person to have this problem, but I cannot
find a solution.
I have a workbook that has 20 worksheets. The orientation varies between
portrait and landscape. I need the landscape worksheets to print with the
footer on the "left" side, so that when I put the entire report (workbook)
together for presentation, all of the footers are on the bottom, like a book.
I have tried merging the first column (A) and making it the footer with the
font facing left, but it is difficult to make them print evenly on each
I have also tried to cop...ploting one cell from 10 worksheets into one chart
i am trying to figure out a way to plot one cell, lets say c-1 from 10
-30 worksheets, in one workbook; on one chart. Each worksheet is a date
and each c-1 value is just a number.
any help would help
Message posted from the Excel Tip Forum at http://www.ExcelTip.com/forum/
-- View and post usenet messages directly from http://www.ExcelTip.com
-- Hundreds of free MS Excel tips, tricks and solutions
You can plot each data point as a separate ser...Link to Worksheet
As soon as I open my worksheet it open 'open dialog box' looking for
file. It has peicharts, trendcharts and data, I don't know which one i
linked. I want to remove the link, how can I
Message posted from http://www.ExcelForum.com
Get a copy of Bill Manville's FindLink program:
You'll be happy once again!
"anupam <" wrote:
> As soon as I open my worksheet it open 'open dialog box' looking for a
> file. It has peicharts, trendcharts and data, I don't know which one is
> linked. I want to r...Import/Export XML in protected worksheet
I developped an excel tool on which most of the cells are protected (and I
need to keep them protected). I mapped an XML schema on all the worksheet
entries. I would like to be able to import and export all these entries but
Excel does not let me do that when the worksheet is protected. Can anybody
...VBA to default printer in Crystal Report
is it possible to set default the printer for crystal report to be the same
as the printer setup in GP (Files>Print Setup)?
eg: is GP is using Printer1, then when calling a crystal report using VBA,
the printer will be set to Printer1. If GP is using Printer2, then crystal
report will be defaulted to Printer2.
thks in advance.
Unfortunately that isn't going to work for a couple reason:
1. The biggest reason is there isn't any way to determine what the printer
is in Dynamics. Not even with Dexterity. It just wasn't something that was
exposed. So I can't think of ...Conditional Formatting in VBA in 2007
I have a series of non-contiguous rows that I want to apply
conditional formatting to. Right now, my code snip looks like this:
With Range(sBegRange, sEndRange)
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & sCFCell & ">40"
.FormatConditions(i).Font.Color = 3
.FormatConditions(i).StopIfTrue = False
Where sCFCell is a string value for the cell I want evaluated for the
formula, and i is an integer that increments for each time I create a
new rule (which I'm doing for each row I format).
It...Can you print specific worksheets?
I have 15 worksheets in my workbook, but i just want to print specific
worksheets. Is it possible to specify which worksheets you want to print to
save me having to print the whole workbook?
You can print each sheet individually (15 separate print)
Select the sheets
(click on the first sheet and ctrl-click on the subsequent sheets)
> Hi everyone,
> I have 15 worksheets in my workbook, but i just want to print specific
> worksheets. Is it possible to specify which worksheets you want to print to
> ...Opening an Excel 2002 worksheet in Excel 2003
I created a spreadsheet using Excel 2002 (XP). I need to open that
spreadsheet using Excel 2003. When I try to open the 2002 spreadsheet in
Excel 2003, I can only see a portion of the spreadsheet and cannot scroll. I
could copy and paste each sheet from the 2002 spreadsheet to a new 2003
spreadsheet, but I cannot even see the tabs at the bottom. How do I open the
2002 spreadsheet in Excel 2003? I do not see a way to convert from the 2002
format to 2003. Thanks. BT
AFAIK, it should?? open fine if 2002 wb saved as microsoft excel
workbook.xls. Try saving immediately upon opening. ??
-...Protect and Share Questions
I would like to share a file which I only want everyone to
be able to view the file and not make changes. But
currently I have the file on the share drive but everyone
has full control also when someone has the file open I am
not able to save changes. Can someone out there give my
some tips to help me out? Any info is good info. Thanks
Do you have your workbook protected? if you use
a password to protect it, be sure to record it someplace
safe so you can get to it later for making changes -
you never know when a little amnesia will hit!! ;)
...Duplicating parts of a worksheet
I am working with an excel worksheet with five columns (account, date
description, purchase order, and expense amount). I want to summariz
from this worksheet all of the expenses by account.
I know how to get the total expenses for each account from this list
but I want to be able to create another worksheet for each account tha
lists date, description, purchase order, and expense amount. Is ther
a way to do this without requiring double entering of everything
Message posted from http://www.ExcelForum.com
This sounds like a job for a Pivottable.
You can create a nice summary repor...loop through all worksheets
My workbook has over 100 worksheets. I want to be able to execute the code
below, have it cycle through all worksheets and when done leave the
worksheet that was active when the code was started active.
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Application.ScreenUpdating = True
Dim ws As Worksheet
Dim wsStart As Worksheet
Set wsStart = ActiveSheet
For Each ws In Worksheet