OLE object error when hiding worksheets that have code to automatically label sheets based on cell value

D_Davis

New Member
Joined
Sep 27, 2011
Messages
10
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,148,140
Messages
5,745,034
Members
423,917
Latest member
Frank1931

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
Top