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
 
Sort by date Sort by votes
mikerickson said:
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
Click to expand...

Mike,
Thank you for this as well.
 
Upvote 0
While putting this all together, I had a conflict with two other macros, one which shows all sheets, and the other which hides all sheets. To solve this I figured out that Public Variables can be declared at the start of the module. Here is my resulting code, which works, but I'm not sure it is perfect.


Code: 
Public Toggle1 As Boolean
Public Toggle2 As Boolean
Public Toggle3 As Boolean
Public Toggle4 As Boolean

Sub ShowAll()

Dim sh As Worksheet

    For Each sh In ActiveWorkbook.Worksheets
        sh.Visible = xlSheetVisible
    Next sh

Toggle1 = True
Toggle2 = True
Toggle3 = True
Toggle4 = True

End Sub


Sub HideAll()

Dim sh As Worksheet

    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> "Inputs" Then sh.Visible = xlSheetHidden
        
    Next sh

Toggle1 = False
Toggle2 = False
Toggle3 = False
Toggle4 = False
    
    
End Sub

Sub TogglePLTrad()
'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

Sub TogglePLVariable()
'Static Toggle2 As Boolean

    If Toggle2 Then
        Toggle2 = False
    Else
        Toggle2 = True
    End If

    Select Case Toggle2
        Case True
            Sheet5.Visible = True
            Sheet4.Visible = True
            Sheet13.Visible = True
            Sheet14.Visible = True
            Sheet15.Visible = True
            Sheet16.Visible = True
            Sheet17.Visible = True
            Sheet20.Visible = True
            Sheet21.Visible = True
        
        Case False
            Sheet5.Visible = False
            Sheet4.Visible = False
            Sheet13.Visible = False
            Sheet14.Visible = False
            Sheet15.Visible = False
            Sheet16.Visible = False
            Sheet17.Visible = False
            Sheet20.Visible = False
            Sheet21.Visible = False

    End Select

End Sub

Sub ToggleOther()
'Static Toggle3 As Boolean

    If Toggle3 Then
        Toggle3 = False
    Else
        Toggle3 = True
    End If

    Select Case Toggle3
        Case True
            Sheet6.Visible = True
            Sheet30.Visible = True
            Sheet22.Visible = True
            
        Case False
            Sheet6.Visible = False
            Sheet30.Visible = False
            Sheet22.Visible = False
    
    End Select

End Sub

Sub ToggleAP()
'Static Toggle4 As Boolean

    If Toggle4 Then
        Toggle4 = False
    Else
        Toggle4 = True
    End If

    Select Case Toggle4
        Case True
            Sheet31.Visible = True
            Sheet23.Visible = True
            Sheet24.Visible = True
            Sheet25.Visible = True
            Sheet26.Visible = True
            Sheet27.Visible = True
            Sheet28.Visible = True
            Sheet29.Visible = True
            Sheet7.Visible = True
            
        Case False
            Sheet31.Visible = False
            Sheet23.Visible = False
            Sheet24.Visible = False
            Sheet25.Visible = False
            Sheet26.Visible = False
            Sheet27.Visible = False
            Sheet28.Visible = False
            Sheet29.Visible = False
            Sheet7.Visible = False
    
    End Select

End Sub
 
Last edited:
Upvote 0
Code: 
If Toggle1 Then
    Toggle1 = False
Else
    Toggle1 = True
End If
can be written
Code: 
Toggle1 = Not(Toggle1)

Code: 
    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
can be written
Code: 
Sheet2.Visible = Toggle1
Sheet3.Visible = Toggle1
Sheet10.Visible = Toggle1
Sheet11.Visible = Toggle1
Sheet12.Visible = Toggle1
Sheet18.Visible = Toggle1
Sheet19.Visible = Toggle1
Sheet8.Visible = Toggle1
Sheet9.Visible = Toggle1

Using boolean variable severly reduces the need to use If Then Else constructions.
 
Upvote 0
Mike,

Thank you for this. Your boolean lesson was very helpful and it all makes sense. This is the first time I will have used boolean logic in vba and I can see how powerful it is.

Code: 
Public Toggle1 As Boolean
Public Toggle2 As Boolean
Public Toggle3 As Boolean
Public Toggle4 As Boolean

Sub ShowAll()

Dim sh As Worksheet

    For Each sh In ActiveWorkbook.Worksheets
        sh.Visible = xlSheetVisible
    Next sh

Toggle1 = True
Toggle2 = True
Toggle3 = True
Toggle4 = True

End Sub


Sub HideAll()

Dim sh As Worksheet

    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> "Inputs" Then sh.Visible = xlSheetHidden
        
    Next sh

Toggle1 = False
Toggle2 = False
Toggle3 = False
Toggle4 = False
    
    
End Sub

Sub TogglePLTrad()

Toggle1 = Not(Toggle1)

            Sheet2.Visible = Toggle1
            Sheet3.Visible = Toggle1
            Sheet10.Visible = Toggle1
            Sheet11.Visible = Toggle1
            Sheet12.Visible = Toggle1
            Sheet18.Visible = Toggle1
            Sheet19.Visible = Toggle1
            Sheet8.Visible = Toggle1
            Sheet9.Visible = Toggle1

End Sub

Sub TogglePLVariable()

Toggle2 = Not(Toggle2)

            Sheet5.Visible = Toggle2
            Sheet4.Visible = Toggle2
            Sheet13.Visible = Toggle2
            Sheet14.Visible = Toggle2
            Sheet15.Visible = Toggle2
            Sheet16.Visible = Toggle2
            Sheet17.Visible = Toggle2
            Sheet20.Visible = Toggle2
            Sheet21.Visible = Toggle2
       
End Sub

Sub ToggleOther()

Toggle3 = Not(Toggle3)
            Sheet6.Visible = Toggle3
            Sheet30.Visible = Toggle3
            Sheet22.Visible = Toggle3
            
End Sub

Sub ToggleAP()

Toggle4 = Not(Toggle4)
            Sheet31.Visible = Toggle4
            Sheet23.Visible = Toggle4
            Sheet24.Visible = Toggle4
            Sheet25.Visible = Toggle4
            Sheet26.Visible = Toggle4
            Sheet27.Visible = Toggle4
            Sheet28.Visible = Toggle4
            Sheet29.Visible = Toggle4
            Sheet7.Visible = Toggle4
            
End Sub
 
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, just wondering why you suggested
Code: 
Names("Toggle1").RefersTo = "=2"
instead of just
Code: 
Names("Toggle1").RefersTo = 2

I get the same results either way, but the latter seems more intuitive (IMHO). Just asking:)

Regards,
Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Remove value from drop down list then enters wrong value in worksheet cell
Replies
2
Views
106
ipbr21054
I
yearego021
  • Solved
VBA to Save as PDF broken with unwanted deleting of tabs.
Replies
7
Views
284
Artik
Artik
E
  • Solved
VBA to print two separate ranges on one sheet of paper
Replies
3
Views
81
EssKayKay
E
A
  • Solved
How do I increase efficiency or stop the lag?
Replies
3
Views
133
Aphten
A
A
  • Question Question
Excel is getting slower as I add more sheets
Replies
3
Views
145
Micron
M

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