Please help with Excel VB question

Hi,

	I'm trying to set a Name range in Excel through VB, by setting the name
property when a specific cell in a worksheet is changed. The code is as follows:

fred1 = CStr("=PL!$A$4:$E$"  Worksheets("Control").Range("maxnumpass").Value)
 ActiveWorkbook.Names.Add Name:="numpasslist", RefersTo:=fred1

My problem is that the change is only active for the worksheet where I change
the cell. Is it possible to ensure that this change takes effect across all
worksheets in the workbook?

	Any and all help appreciated either in a reply to this post or on
jgallagher@NOSPAMindigo.ie (remove the NOSPAM for a valid address)

		Thanks in advance,

			Jim

0
1/14/2004 7:45:20 PM
excel 39879 articles. 2 followers. Follow

2 Replies
575 Views

Similar Articles

[PageSpeed] 23

You could write a for next....loop statement that would select each
worksheet and paste the string in each sheet.:

dim x as integer
for x = 1 to (number of worksheets)
sheets("sheet"& (x)).select
your code here
next x

You need to have a consistent naming structure to use this or you can assign
the names of the sheets to variables and use is it that way.

Scott
"Jim Gallagher" <jim.hates.spam@nospam.invalid> wrote in message
news:oo6b0019bvhm2qjrc5ikmiqlo9h1p6okd9@4ax.com...
> Hi,
>
> I'm trying to set a Name range in Excel through VB, by setting the name
> property when a specific cell in a worksheet is changed. The code is as
follows:
>
> fred1 = CStr("=PL!$A$4:$E$"
Worksheets("Control").Range("maxnumpass").Value)
>  ActiveWorkbook.Names.Add Name:="numpasslist", RefersTo:=fred1
>
> My problem is that the change is only active for the worksheet where I
change
> the cell. Is it possible to ensure that this change takes effect across
all
> worksheets in the workbook?
>
> Any and all help appreciated either in a reply to this post or on
> jgallagher@NOSPAMindigo.ie (remove the NOSPAM for a valid address)
>
> Thanks in advance,
>
> Jim
>


0
sks (1)
1/14/2004 9:16:52 PM
On Wed, 14 Jan 2004 13:16:52 -0800, "Scott Simpson" <sks@att.com> wrote:

>You could write a for next....loop statement that would select each
>worksheet and paste the string in each sheet.:
>
>dim x as integer
>for x = 1 to (number of worksheets)
>sheets("sheet"& (x)).select
>your code here
>next x
>
>You need to have a consistent naming structure to use this or you can assign
>the names of the sheets to variables and use is it that way.
>
>

Never thought of that! Thanks a million.

	Jim

0
1/14/2004 9:31:54 PM
Reply:

Similar Artilces:

CToolBar Help
Hello, I've been trying to add a toolbar to my dialog following the example from MSDN a http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vcmfc98/html/_mfc_ctoolbar.asp But for some reason I just can't add the toolbar I created. Here is my code: CToolBar m_toolbar; if (!m_toolbar.Create(this, WS_CHILD | WS_VISIBLE | CBRS_TOP | CBRS_GRIPPER | CBRS_TOOLTIPS | CBRS_FLYBY | CBRS_SIZE_DYNAMIC, IDR_TOOLBAR1)) { MessageBox("Error -1"); } m_toolbar.LoadToolBar(IDR_TOOLBAR1); Could somebody please tell what, if anything, I am doing wrong? Thank you, V...

Cell shading in excel doesn't show.
Does anyone why on my form the Cell Shading isn't visible, but when I do print preview it is there. How do I turn on the Cell Shading so its visible when I'm working on the spreadsheet? Maybe this will help: http://support.microsoft.com/?id=320531 Regards Rowan joecec wrote: > Does anyone why on my form the Cell Shading isn't visible, but when I do > print preview it is there. How do I turn on the Cell Shading so its visible > when I'm working on the spreadsheet? ...

Office 2007 Professional: Help Files Issues
Gents, I cannot find a solution to this odd issue and before I submit a support ticket I thought I would ask the community. We have office 2007 professional (volume licensing) on a Window 2003 terminal server. We have a few users out of a bunch that get the "This page is unavailable" I have tried the Refresh and back. I've tried online and offline modes. In offline I get the table of contents to the left but same unavailable message when I click the links. I noticed on testing some other users, that apparently never used the help. That there are some prompts...

Outlook 2003 Messages and Calendar Appts Received as PLAIN TEXT
SGVsbG86DQoNCkkganVzdCBnb3QgYSBuZXcgbGFwdG9wIHdpdGggVmlzdGEgSG9tZSBCYXNpYyBv biBpdCBhbmQgSSBjb3BpZWQgbXkNCnBzdCBmaWxlIGZyb20gbXkgb2xkIGxhcHRvcCB0byB0aGUg bmV3IG9uZS4gRXZlcnl0aGluZyB3b3JrcyBmaW5lDQpleGNlcHQgYWxsIGUtbWFpbCBtZXNzYWdl cyBhbmQgY2FsZW5kYXIgYXBwdHMuIEkgcmVjZWl2ZWQgYXJlIGluIHBsYWluDQp0ZXh0LiBJJ3Zl IHNlYXJjaGVkIGV2ZXJ5d2hlcmUgZm9yIHRoaXMgZml4LCBidXQgZm91bmQgbm90aGluZy4gDQoN CkRvZXMgYW55b25lIGhhdmUgYW55IHN1Z2dlc3Rpb25zIGZvciBtZT8gDQoNClRoYW5rIHlvdSEN ClNwdWRkeQ0K me@cox.net <me@cox.net> wrote: > I just got a new laptop with Vista Home Basic on it and I copied my > pst file...

Spell Check Help
I have this code in the AfterUpdate in a TextBox set for a memo: If Len(Me!casefacts & "") > 0 Then DoCmd.RunCommand acCmdSpelling Else Exit Sub End If everytime the spell check executs, it check the whole form. How can I get it to just check that one textbox and not everything else. bladelock wrote: >I have this code in the AfterUpdate in a TextBox set for a memo: > >If Len(Me!casefacts & "") > 0 Then > DoCmd.RunCommand acCmdSpelling > Else > Exit Sub >End If &...

Help with Regexp, please
Hi, The regular expression (\d{15,16}) matches a substring in a cell. I want to extract the remaining part of the cell ie. from the character after the matched substring till the end of the string in the cell using a regular expression. Is it possible to do this? Thanks in advance for the help. Regards, Raj Raj, with abcd15,16xyz in a cell the code below abstracts abcxyz. Maybe you can use this as a basis to develop your code Function GetSubstring(S As String) As String Dim re As Object Set re = CreateObject("vbscript.regexp") re.Pattern = &quo...

Default Value on Form Help
I have a query that calculates the total of a field in my table - Field Name in my Table is Total Value which calculates the total for a series of records that has the same invoice number. Field in in my query is Aggregate Value. I now have a form where I bring in the Aggregate Value. I need to create another Text box Value Verified where I bring in the Aggregate Value as a default, but I may have to change it if the Value has changed - but the Aggregate Value has to remain the same. I tried using default value on the form for the Value Verified Text control that brings in ...

Publisher 2007 Trial Help
I installed the trial version of Publisher 2007 and want to uninstall it now. I have tried to remove in the "add/remove" programs section. And I have also tried a suggestion I found in an earlier post about going to the "installer" and trying to uninstall it there. Oh and I tried repairing it and then trying to uninstall it. I can't even open the program from my programs menu and I can't get into the language section to check the language. It tells me that the language isn't compatible with my version or something to that effect. Nothing has worked...

MS Money 2005 Question
Hello all; I have installed MS Money 2005 on my Win XP SP2 machine. I live in Canada. I have my regional settings in the machine pegged to Canada. I have told MS Money in the settings area to Set Planner Currency to Canada, and I have also set Base Currency to Canada. Here is the issue. When I create a new inventory item and try to make a invoice for that item it display's a different price. Example: I have made a product called Test. I have pegged a $12.00 price tag to this item and have taken the check mark out of Tax, so no tax. When I add this item to my invoice it ...

PLEASE PLEASE HELP
I Have an emergency... I deleted a macro !!! I have 60 rows with checkboxes on cells A1 trough A60 that automatically as checked were inserting current dates on the cell next to it. for some reason a disaster happened and the macro was deleted or messed up. I tried searching for help in these groups, but none so far. I found one, but it is not a macro besides when I use that function all the other previous dates keep changing when other checkboxes are checked on in different days of the week. If you can please guide me to the right answer I will appreciated. Hope I made sense with my explana...

Cannot access a file -- HELP
I have this problem at work (using Excel 2000 Windows 2000) and at home (Excel 2003, Windows XP Pro). SOMETIMES...not always, I try to access a file on a network drive I get the message: "path..filename could not be found. Check the spelling of the file name and verify that the file location is correct". Once I get the message on that file, I always get it on the file....but there might be 27 others in the same folder that I can open. And I get the message if I try to open the file via explorer or via Excel. If I copy the file to my C: drive it opens fine. I even tried t...

excel 'file format is not valid'
I have an excel file which was retrieved from a computer running Win 98. This computer crashed and when the file was retrieved it will not open in any version of excel. I keep getting the error message 'file format is not valid'. The file does open in wordpad but this is gobbleygook and would take ages to get the data reorganised. Can anyone help? There are no viruses in the file - it just will not open. You might be able to recover it by using another program--both xl2002 and OpenOffice have better reputations for opening files that earlier versions of excel couldn't. (ht...

Sum Function Help Please
I have created two Option Groups, setting up values against the radio buttons. The output from both boxes works fine (thanks to the Wizard), however when I'm creating a formula on the report, I'm unable to add the two returned values together. If I type in =([Q1]+[Q2]) then I get both numbers concatenated together (ie returned value from Q1 is 1 and returned value from Q2 is 2, the answer I get is 12 !!!!.) Interestingly if I enter =([Q1]-[Q2]) then I get the correct answer. Any help would be greatly appreciated here. I could be wrong, but it seems like access i...

2nd request for help -PLEASE PLEASE PLEASE
went through a windows update and was asked to reboot which I did. I had Outlook open and closed it before rebooting - it was opened to contacts. when I tried to access Outlook I got an error message saying "your personal address book could not be opened. the file is either not accessible or not a personal address book. choose retry to access your personal address book, or choose cancel to not open your personal address book with this application." I have no other application to open this address book with! my contact info is detrumental to me as well as my Calendar ...

In Word How do I switch off auto date insertion please
How can I disable? ...

Is this the right news group for Outlook Web Access questions?
I am trying to find a way to add search folders in Web Access for 'For Follow up' and for 'unread' as I have int he real client. Cheers. Robin No, an Exchange group would be better suited as OWA is a part of Exchange, not Outlook. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Robin Jackson asked: | I am trying to find a way to add search folders in Web Access for 'For | Follow up' and for 'unread&...

Remove template help task pane from startup
I created an invoice using one of Microsoft's templates. The help template appeared when I was designing the invoice. Now that the invoice is finished, I would like for the help template to NOT appear. I went to Tools-Options and unchecked "startup task pane" and also went to Tools-Customize and unchecked "Task Pane". This made the task pane go away, but only until I opened it again. Is there any way to make it not show up every time I open the document? ...

How do I make a drop down list to pick from for a cell in Excel
http://www.contextures.com/xlDataVal01.html -- HTH Bob Phillips "bbiernbaum" <bbiernbaum@discussions.microsoft.com> wrote in message news:D5D2240C-7871-4AF4-A015-E6E775E53806@microsoft.com... > Look up Validation in XL Help ...

Can't SEND SSL email out!! Please Help... it HURTZ!
OKay... being a bit silly about he hurting part. I have a strange email issue with some of my computers at home that I use for work. We have 4 computers. 2 of them work FINE, the other 2 have issues. I know the email accounts are working correctly because if I run them on the 2 *good* computers they work like they should. Configurations: (GOOD Notebooks) 1 - HP AMD-XP notebook with WinXP-Home SP1, Office 2000 2 - Sony P4 Notebook with WinXP-Home SP1, Office 2000 (Bad systems) 3 - Dell P3 notebook with WinXP-Pro SP1, Office 2002 4 - Compaq AMD-XP with WinXP-Home SP2, Office 2000 ALl ma...

Please Please Please help
Hi I have a Worksheet with option buttons and I need the users to be able to click on the options but not be able to drag or move them. I don't want the users to be able to click into or type anything in any of the cells - only these options buttons???? How is it done. I have tried unlocking all cells and then locking the individual ones, but the user can still drag the option button around and they could do this by mistake thus upsetting the design of my sheet! I have tried protecting the sheet for Objects - but then I can't click on the option at all ....Please someone help...

Help with formula #3
I need to edit this formula 2 ways =COUNT(J1:J5) first way I need it to count any cell with a value lower then 10 as 1 and any cell with a value of 10 or higher as .75 For example if cells j1 to J4 are all valued at 6 but cell J5 is value at 11 the total I get would be 4.75 Second formula I need is to count J1 to J5 only the cells with a value of 10 or higher as .25 For example if cells J1 to J4 are all valued at 6 but cell J5 is value at 11 the total I get would be .25 Hi Leave Alone! First problem: =COUNTIF(J2:J6,"<10")+COUNTIF(J2:J6,">=10")*0.75 Second pro...

A bug in excel
Hi, I found a bug in excel 2000. I tried to replace all ? with 0 in a column. It replaced all the cells to be 0, even the cells with other values that were not ?. Chris ? is a wild card. It's used to represent any character. Try replacing ~? with 0. The tilde says to treat the question mark as a question mark. (Same kind of thing with asterisk (*).) yma wrote: > > Hi, > > I found a bug in excel 2000. I tried to replace all ? with 0 in a column. > It replaced all the cells to be 0, even the cells with other values that > were not ?. > > Chris -- Da...

Help with series data
I am trying to create a sheet.. But In A1 i need to to look like this: TOTE20000 TOTE20000 TOTE20000 TOTE20000 TOTE20001 TOTE20001 TOTE20001 TOTE20001 BUT i need to keep this series up to TOTE24999 Can anyone help me with a simple solution? It wont see the pattern with TOTE infront but will with just the numbers, But when I do a Replace it starts chunkign the numbers to decimals. I appreciate any help! I have to read between the lines a bit because I'm not really clear on what you want. Let's say you want to enter 20000 in the cell, but you want it to be displayed as TOTE20000...

I need help with SQL please
I have a form with a group box on it. The code for it is: 20 Select Case grpPlan.Value Case 1 30 strMon = "WorkRec" 40 Case 2 50 strMon = "IntRec" 60 Case 3 70 strMon = "FinalRec" 80 End Select After I select the one I want then the SQL is build based on the selection and a report is generated. strWhere = "EmpSuper.WorkRec=""" & strMon & _ """ or EmpSuper.IntRec=""" & strMon & _ &quo...

Help with vlookup results for summary
I gotta vlookup table and on tha other worksheets people enter the info from dropdown boxes. I need to show the books sold and the average of looks at it after being chosen to be listed as a summary. A B C red book $15 x Blue book $10 x red book $20 w So if a "red book" "$15", a "blue book" "$10" and a "red book" "$20" was sold, and I want to list red book or W books, how can I display this summary info? It should look something like this so i can use the figures for sums and division. Red Book | 2 | ...