Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA x1SheetVeryHidden/xlSheetVisible Toggle Button 1

Status
Not open for further replies.

knifey

Technical User
Nov 14, 2006
180
GB
Hi,
I'm trying to create a simple toggle to hide or unhide sheets using VBA but I can't get the code below to work. It gives a 'With or object variable not set' error as the wksheet variable is set to nothing.
Can anyone give me a pointer?
Thanks,
Roy

Sub ToggleIDS()
Dim wksheet As Worksheet
With wksheet
If .Name = "Incorrectly Requested" Then
If .Visible Then
.Visible = xlSheetVeryHidden
ElseIf Not .Visible Then
.Visible = xlSheetVisible
End If
End If
End With
End Sub
 
Hi again,
No response needed for this now, I came up with the following code which works great.
Cheers,
Roy

Sub ToggleIDS2()
Dim wksheet As Worksheet
Set wksheet = Sheets("Incorrectly Requested")
With wksheet
wksheet.Visible = Not wksheet.Visible
End With
End Sub
 





Hi,

In this simple sub, there is no reason at all to set an object...
Code:
Sub ToggleIDS2()
With Sheets("Incorrectly Requested")
  If .Visible = xlsheetvisible then
     .visible = xlsheetveryhidden
  else
     .visible = xlsheetvisible
  end if
End with
End Sub
and to make it a more general procedure...
Code:
Sub ToggleIDS2(sName as string)
With Sheets(sName)
  If .Visible = xlsheetvisible then
     .visible = xlsheetveryhidden
  else
     .visible = xlsheetvisible
  end if
End with
End Sub


Skip,

[glasses] To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top