Including a named range in VBA

T

Todd

I have 2 seperate worksheets. On the first is a series of
command check boxes. On the second worksheet is a normal
spreadsheet with cell ranges that I have named for example
range a10:f10, g20:h80 would be named AddressChange. What
I would like to do is when one of the command buttons is
selected this named range will be highlighted on the
spreadhsheet. What VBA code can I use to make this
happen? Thanks for the help!
 
J

JulieD

Hi Todd

something along the lines of

Sub Button1_Click()
Worksheets("Sheet2").Activate
Range("AddressChange").Select
End Sub

Hope this helps
Cheers
JulieD
 
T

Todd

That didn't seem to work. This is the code I have using
the code you gave. Do you see what is wrong?

Public Sub CheckBox1_Click()
Worksheets("Form").Activate
Range("AddressChange").Select
If Selection.Interior.Pattern = xlNone Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Else
With Selection.Interior
.Pattern = xlNone
End With
End If
End Sub
 
J

JulieD

Hi Todd

using your code as posted below gave me fill colour of bright yellow

so couple of questions
- under insert / name / define when you click on "AddressChange" what does
it say in the "refers to" box
- are you sure that the sheet tab name "Form" doens't have a leading or
trailing space
- are macros enabled under tools / macro / security
- does it work if you change the fill colour index to another number

if none of these are your problem then what happens when run your code with
a breakpoint added at the first line-
in the VBE Window click on Worksheets("Form").Activate and press F9
now switch back (Alt & F11) to your workbook
press the button to run the code,
it should take you into the VBE Window with the first line highlighted in
yellow,
use the F8 key to step through the code .. can you see what's going on

to remove the breakpoint press F9 again

Let us know how you get on

Cheers
JulieD
 
T

Todd

None of your suggested problems seem to be the problem.
Everytime I run the code it goes back to the
Range("AddressChange").Select The name of the range is
correct. Any other possibilities?
 
J

JulieD

Hi Todd

if you would like to zip & email me the file direct i'll take a look at it

julied_ng at hcts dot net dot au

its 1am here so i'll only be up for the next 1/2hr or so.

Cheers
JulieD
 
D

Debra Dalgleish

Include the sheet name before the range name, e.g.:

Worksheets("Form").Range("AddressChange").Select
 
T

Todd

My email keeps getting returned.
-----Original Message-----
Hi Todd

if you would like to zip & email me the file direct i'll take a look at it

julied_ng at hcts dot net dot au

its 1am here so i'll only be up for the next 1/2hr or so.

Cheers
JulieD




.
Click to expand...
 
Top