VBA change value of named constant

R

Risk

Board Regular
Joined
Jul 27, 2006
Messages
71
I'm new to vba but trying to learn. I'm stuck on changing the value of a named range. Any ideas for a beginner?

Code: 
Sub test()


If [Toggle1] = 1 Then [Toggle1] = 0 Else [Toggle1] = 1
      
    Select Case [Toggle1]
        Case Is = 1
        
            Sheet2.Visible = True
            Sheet3.Visible = True
            Sheet10.Visible = True
            Sheet11.Visible = True
            Sheet12.Visible = True
            Sheet18.Visible = True
            Sheet19.Visible = True
            Sheet8.Visible = True
            Sheet9.Visible = True
                
        Case Is = 0
        
            Sheet2.Visible = False
            Sheet3.Visible = False
            Sheet10.Visible = False
            Sheet11.Visible = False
            Sheet12.Visible = False
            Sheet18.Visible = False
            Sheet19.Visible = False
            Sheet8.Visible = False
            Sheet9.Visible = False
        
    End Select

End Sub
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
What is your named range called?
 
Upvote 0
Norie said:
What is your named range called?
Click to expand...

Toggle1

yuxOv.png
 
Upvote 0
That's not really a named range, it's a named value.

You could change what it refers to like this.
Code: 
Names("Toggle1").RefersTo = "=2"
PS Why are you using this 'constant'?
 
Upvote 0
Norie said:
That's not really a named range, it's a named value.

You could change what it refers to like this.
Code: 
Names("Toggle1").RefersTo = "=2"
PS Why are you using this 'constant'?
Click to expand...

Norie, Thanks for the response. I'm using this as a toggle switch to show/hide a group of worksheets. I figured the name value would be a good on/off switch. Nevertheless, I cant seem to make it work.

Code: 
Sub test()


If Names("Toggle1").RefersTo = 1 Then Names("Toggle1").RefersTo = 0 Else Names("Toggle1").RefersTo = 1
      
    Select Case [toggle1]
        Case Is = 1
        
            Sheet2.Visible = True
            Sheet3.Visible = True
            Sheet10.Visible = True
            Sheet11.Visible = True
            Sheet12.Visible = True
            Sheet18.Visible = True
            Sheet19.Visible = True
            Sheet8.Visible = True
            Sheet9.Visible = True
                
        Case Is = 0
        
            Sheet2.Visible = False
            Sheet3.Visible = False
            Sheet10.Visible = False
            Sheet11.Visible = False
            Sheet12.Visible = False
            Sheet18.Visible = False
            Sheet19.Visible = False
            Sheet8.Visible = False
            Sheet9.Visible = False
        
    End Select

End Sub
 
Upvote 0
Names("Toggle1").Refers to will return '=1', not 1.

If this is for toggling sheets visibilty.
Code: 
    Sheet2.Visible = Not Sheet2.Visible
            Sheet3.Visible =  Not Sheet3.Visible

            Sheet10.Visible =  Not Sheet10.Visible

            Sheet11.Visible =  Not Sheet11.Visible

            Sheet12.Visible =  Not Sheet12.Visible

            Sheet18.Visible =  Not Sheet18.Visible

            Sheet19.Visible =  Not Sheet19.Visible

            Sheet8.Visible =  Not Sheet8.Visible

            Sheet9.Visible =  Not Sheet9.Visible
 
Upvote 0
Norie said:
Names("Toggle1").Refers to will return '=1', not 1.

If this is for toggling sheets visibilty.
Code: 
    Sheet2.Visible = Not Sheet2.Visible
            Sheet3.Visible =  Not Sheet3.Visible

            Sheet10.Visible =  Not Sheet10.Visible

            Sheet11.Visible =  Not Sheet11.Visible

            Sheet12.Visible =  Not Sheet12.Visible

            Sheet18.Visible =  Not Sheet18.Visible

            Sheet19.Visible =  Not Sheet19.Visible

            Sheet8.Visible =  Not Sheet8.Visible

            Sheet9.Visible =  Not Sheet9.Visible
Click to expand...

Norie,

That is exactly the same approach I took when I tried the toggle

Code: 
Sub TogglePLTrad()

    If Sheet2.Visible = False Then
        
        Sheet2.Visible = True
        Sheet3.Visible = True
        Sheet10.Visible = True
        Sheet11.Visible = True
        Sheet12.Visible = True
        Sheet18.Visible = True
        Sheet19.Visible = True
        Sheet8.Visible = True
        Sheet9.Visible = True
        
        Else
            Sheet2.Visible = False
            Sheet3.Visible = False
            Sheet10.Visible = False
            Sheet11.Visible = False
            Sheet12.Visible = False
            Sheet18.Visible = False
            Sheet19.Visible = False
            Sheet8.Visible = False
            Sheet9.Visible = False
    End If

End Sub

I know code is not always 100% fail proof, but I kept thinking what if somebody hide sheet2 and then tried to run the macro. This lead me to consider this other approach of a named formula that would flip values between 1 and 0.

Now using your syntax fix, the code works grand :)

Code: 
Sub test()


If Names("Toggle1").RefersTo = "=1" Then Names("Toggle1").RefersTo = "=0" Else Names("Toggle1").RefersTo = "=1"
      
    Select Case [toggle1]
        Case Is = 1
        
            Sheet2.Visible = True
            Sheet3.Visible = True
            Sheet10.Visible = True
            Sheet11.Visible = True
            Sheet12.Visible = True
            Sheet18.Visible = True
            Sheet19.Visible = True
            Sheet8.Visible = True
            Sheet9.Visible = True
                
        Case Is = 0
        
            Sheet2.Visible = False
            Sheet3.Visible = False
            Sheet10.Visible = False
            Sheet11.Visible = False
            Sheet12.Visible = False
            Sheet18.Visible = False
            Sheet19.Visible = False
            Sheet8.Visible = False
            Sheet9.Visible = False
        
    End Select

End Sub



If I may ask a final question. I was under the impression one could code this using a Boolean approach since 1 = True and 0 = False.

Code: 
 If [Toggle1] Then [Toggle1] = 0 Else [Toggle1] = 1

I believe my understand of using the named manager in vba is incomplete.
 
Upvote 0
Instead of a named range why not use a static variable?

Code: 
Sub test()
Static Toggle1 As Boolean

    If Toggle1 Then
        Toggle1 = False
    Else
        Toggle1 = True
    End If

    Select Case Toggle1
        Case True
            Sheet2.Visible = True
            Sheet3.Visible = True
            Sheet10.Visible = True
            Sheet11.Visible = True
            Sheet12.Visible = True
            Sheet18.Visible = True
            Sheet19.Visible = True
            Sheet8.Visible = True
            Sheet9.Visible = True
        Case False
            Sheet2.Visible = False
            Sheet3.Visible = False
            Sheet10.Visible = False
            Sheet11.Visible = False
            Sheet12.Visible = False
            Sheet18.Visible = False
            Sheet19.Visible = False
            Sheet8.Visible = False
            Sheet9.Visible = False
    End Select

End Sub
 
Upvote 0
Instead of 0/1 you can use true/false

Code: 
With ThisWorkbook
    .Names.Add Name:="Toggle", RefersTo:="=" & Not (Evaluate(.Names("Toggle").RefersTo))
End With
or if you want 0/1

Code: 
With ThisWorkbook
    .Names.Add Name:="Toggle", RefersTo:="=" & (1 - (Evaluate(.Names("Toggle").RefersTo)))
End With
 
Upvote 0
Norie said:
Instead of a named range why not use a static variable?

Code: 
Sub test()
Static Toggle1 As Boolean

    If Toggle1 Then
        Toggle1 = False
    Else
        Toggle1 = True
    End If

    Select Case Toggle1
        Case True
            Sheet2.Visible = True
            Sheet3.Visible = True
            Sheet10.Visible = True
            Sheet11.Visible = True
            Sheet12.Visible = True
            Sheet18.Visible = True
            Sheet19.Visible = True
            Sheet8.Visible = True
            Sheet9.Visible = True
        Case False
            Sheet2.Visible = False
            Sheet3.Visible = False
            Sheet10.Visible = False
            Sheet11.Visible = False
            Sheet12.Visible = False
            Sheet18.Visible = False
            Sheet19.Visible = False
            Sheet8.Visible = False
            Sheet9.Visible = False
    End Select

End Sub
Click to expand...

Norie,
This is exactly what I wanted to do, but I had no idea about static variables, hence my trying to fashion a named range as a static variable. Thank you, I've learned a good bit due to your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Worksheet Change - multiple Loops in one sub
2
Replies
15
Views
396
mumps
mumps
N
  • Question
ActiveX Checkbox fails after sheetname change
Replies
4
Views
186
bobsan42
bobsan42
U
  • Solved
Newbie Seeking Help with Ranges, Loops, and Select
2 3
Replies
27
Views
497
USNA91
U
P
  • Question
Help With VBA Script
Replies
0
Views
181
Pirate_tat87
P
sparky2205
  • Question
ListBox not highlighting selection
Replies
0
Views
96
sparky2205
sparky2205

Forum statistics

Threads
1,216,074
Messages
6,128,654
Members
449,462
Latest member
Chislobog

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top