Please help with Excel VB question

  • Thread starter Thread starter Jim Gallagher
  • Start date Start date
J

Jim Gallagher

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
(e-mail address removed) (remove the NOSPAM for a valid address)

Thanks in advance,

Jim
 
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
 
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.
Click to expand...

Never thought of that! Thanks a million.

Jim
 
Back
Top