Hey Guys,
First of all Happy New Year to everyone and hope all is well. I apologize for the long thread title but best describes the issue I'm having. Here is the breakdown, I have a workbook that I'm creating as a pre-sales tool for my company and I need it to do a number of functions (most of which i have working) to make it easy and "fool-proof" for users. The specific problems I'm having are as follows
I have code to automatically unhide/hide sheets based on combobox selection that works. however I've added code to label said sheets once they are unhid using a cell value which works. however when I try to hide the sheet (I have this programmed as a reset button on the main sheet) I now get an error. I also get it when i now try to unhide them also and i suspect that its because of the name of the sheet in the code i.e. "site" since the sheets were originally named site 1 to site 25
This is the code to unhide the sheets:
"Private Sub ComboBox2_Change()
Dim i As Integer
If ComboBox2.Value <> "" Then
Application.ScreenUpdating = False
For i = 1 To ComboBox2.Value
Sheets("Site" & (i)).Visible = True
Next i
Application.ScreenUpdating = True
End If
End Sub"
This is the code to hide them
"Dim Sh As Shape
For Each Sh In ActiveSheet.Shapes
If TypeName(Sh.OLEFormat.Object) = "TextBox1" Then
Sh.OLEFormat.Object.Characters.Text = ""
End If
Next Sh
Dim ole As OLEObject
'to clear control toolbox checkboxes using cmd button
For Each ole In Worksheets("Start").OLEObjects
If ole.progID = "Forms.CheckBox.1" Then
ole.Object.Value = 0
End If
Next
Sheets("Start").Select
Sheets("Data").Visible = False
Sheets("Configure System").Visible = False
Sheets("Administer System").Visible = False
Sheets("Manage Users and Groups").Visible = False
Sheets("Program Applications").Visible = False
Sheets("Maintain and Troubleshoot").Visible = False
Sheets("Mitel").Visible = False
Sheets("Avaya").Visible = False
Sheets("Start").Visible = True
Sheets("Site1").Visible = False
Sheets("Site2").Visible = False
Sheets("Site3").Visible = False
Sheets("Site4").Visible = False
Sheets("Site5").Visible = False
Sheets("Site6").Visible = False
Sheets("Site7").Visible = False
Sheets("Site8").Visible = False
Sheets("Site9").Visible = False
Sheets("Site10").Visible = False
Sheets("Site11").Visible = False
Sheets("Site12").Visible = False
Sheets("Site13").Visible = False
Sheets("Site14").Visible = False
Sheets("Site15").Visible = False
Sheets("Site16").Visible = False
Sheets("Site17").Visible = False
Sheets("Site18").Visible = False
Sheets("Site19").Visible = False
Sheets("Site20").Visible = False
Sheets("Site21").Visible = False
Sheets("Site22").Visible = False
Sheets("Site23").Visible = False
Sheets("Site24").Visible = False
Sheets("Site25").Visible = False
End Sub
I need to find a way to hide/unhide but allow for the sheet names to be dynamic as well. Not sure how to do this and would really appreciate some assistance.
Thanks in advance
First of all Happy New Year to everyone and hope all is well. I apologize for the long thread title but best describes the issue I'm having. Here is the breakdown, I have a workbook that I'm creating as a pre-sales tool for my company and I need it to do a number of functions (most of which i have working) to make it easy and "fool-proof" for users. The specific problems I'm having are as follows
I have code to automatically unhide/hide sheets based on combobox selection that works. however I've added code to label said sheets once they are unhid using a cell value which works. however when I try to hide the sheet (I have this programmed as a reset button on the main sheet) I now get an error. I also get it when i now try to unhide them also and i suspect that its because of the name of the sheet in the code i.e. "site" since the sheets were originally named site 1 to site 25
This is the code to unhide the sheets:
"Private Sub ComboBox2_Change()
Dim i As Integer
If ComboBox2.Value <> "" Then
Application.ScreenUpdating = False
For i = 1 To ComboBox2.Value
Sheets("Site" & (i)).Visible = True
Next i
Application.ScreenUpdating = True
End If
End Sub"
This is the code to hide them
"Dim Sh As Shape
For Each Sh In ActiveSheet.Shapes
If TypeName(Sh.OLEFormat.Object) = "TextBox1" Then
Sh.OLEFormat.Object.Characters.Text = ""
End If
Next Sh
Dim ole As OLEObject
'to clear control toolbox checkboxes using cmd button
For Each ole In Worksheets("Start").OLEObjects
If ole.progID = "Forms.CheckBox.1" Then
ole.Object.Value = 0
End If
Next
Sheets("Start").Select
Sheets("Data").Visible = False
Sheets("Configure System").Visible = False
Sheets("Administer System").Visible = False
Sheets("Manage Users and Groups").Visible = False
Sheets("Program Applications").Visible = False
Sheets("Maintain and Troubleshoot").Visible = False
Sheets("Mitel").Visible = False
Sheets("Avaya").Visible = False
Sheets("Start").Visible = True
Sheets("Site1").Visible = False
Sheets("Site2").Visible = False
Sheets("Site3").Visible = False
Sheets("Site4").Visible = False
Sheets("Site5").Visible = False
Sheets("Site6").Visible = False
Sheets("Site7").Visible = False
Sheets("Site8").Visible = False
Sheets("Site9").Visible = False
Sheets("Site10").Visible = False
Sheets("Site11").Visible = False
Sheets("Site12").Visible = False
Sheets("Site13").Visible = False
Sheets("Site14").Visible = False
Sheets("Site15").Visible = False
Sheets("Site16").Visible = False
Sheets("Site17").Visible = False
Sheets("Site18").Visible = False
Sheets("Site19").Visible = False
Sheets("Site20").Visible = False
Sheets("Site21").Visible = False
Sheets("Site22").Visible = False
Sheets("Site23").Visible = False
Sheets("Site24").Visible = False
Sheets("Site25").Visible = False
End Sub
I need to find a way to hide/unhide but allow for the sheet names to be dynamic as well. Not sure how to do this and would really appreciate some assistance.
Thanks in advance
