Hiding columns and sheets based on combo-box values

PatrickW · Nov 4, 2008

Hi, all

As a VBA incompetent, I'm butting up against problems that I can see a logical solution to, but that I have no idea how to code, and I would very much appreciate assistance if possible.

I have an activeX combo-box that selects from different pieces of equipment that we supply. Based on that selection, I require ranges from the same page that the combo-box is on to either hide or unhide. Also, I require different tabs to become visible or hidden based on that same selection. So far so good - I have code that does this, and it appears to work without glitch.

Where the problem arises, is in one of the ranges that is unhidden when a particular piece of equipment is selected there is another combo-box that I would like to use (the number of said pieces of equipment to supply) to further hide/unhide additional ranges on the same page, and also hide/unhide certain tabs as well.

When I make a selection from combo-box 1, all works as planned, but when I change the state of combo-box 2, even with no associated coding referring to it, I cannot change combo-box 1 again without getting Error 1004 "Unable to get the Hidden property of the range class".

None of the sheets in the workbook are protected.

I would sincerely appreciate any help/code that could circumvent this error.

Regards

PatrickW

PatrickW · Nov 4, 2008

Does anyone have any possible solutions, please?

I've gone through many of the partly-related posts, but can't seem to find anything that addresses this problem.

Thanks.

PatrickW · Nov 5, 2008

Anyone have any insights into this problem? Is more information required?

I'd really appreciate any assistance.

Thanks

jindon · Nov 5, 2008

We need to see your code to give you any suggestion.

I suspect, "Select/Activate", is giving you a hard time...

PatrickW · Nov 5, 2008

Ok - sure thing.

Herewith the code used for the first combo box, that I received from a friend, that seems to work just fine at the moment.

[face=Courier New]Private Sub ComboBox1_Change()

Dim aWks As Worksheet, s As Shape, Alwayshidden As Range
Set Alwayshidden = Range("X:AA") ' Define the range to be permanently hidden
For Each aWks In ThisWorkbook.Worksheets
If aWks.Name <> ComboBox1.Parent.Name Then ' don't hide the sheet with the combo box
If InStr(1, aWks.Name, ComboBox1.List(ComboBox1.ListIndex)) Then
aWks.Visible = xlSheetVisible
Else
aWks.Visible = xlSheetHidden
End If
End If
Next aWks
Select Case ComboBox1.Text

Case "Thickener"
Dim rngThickener As Range
Set rngThickener = Range("I:L, R:BE")

Columns.Hidden = False
rngThickener.EntireColumn.Hidden = True

Alwayshidden.EntireColumn.Hidden = True ' Hide the data validation selection columns

For Each s In ActiveSheet.Shapes
If Left(s.Name, 8) = "CheckBox" Then
With ActiveSheet.OLEObjects(s.Name)
If rngThickener.EntireColumn.Hidden = False Then
.Visible = True
Else
.Visible = False
End If
End With
End If
Next s
End Select
End Sub[/face]

The problem then arises because I have another combo-box, that is in one of the ranges that is unhidden based on the selection of combo-box. Even though there is no code yet associated with this second combo box, when I change its values (from the default to "1" or "2" based on the list fill range) I cannot change combo-box 1 again, without getting error 1004.

I hope this makes sense.

Thanks

jindon · Nov 5, 2008

Then what about ComboBox2_Change() ?

PatrickW · Nov 5, 2008

I had attempted to code something in the same vein as for what's written for combobox1, but it's horrible code - brute force coding.

I'll show you what I've got below: what I'm trying to achieve here is for data sheets in certain columns, on the same sheet as combobox 1 and 2, to unhide based on the number of units selected in combobox 2, as well as the required certain costing sheet tabs to unhide based on combobox 2.

All seems to work ok (although I'd love to see the elegant concise code to replace this that I know is out there, somewhere) until I change the value in combobox 1 again, once I've changed combobox 2.

Private Sub ComboBox2_Change() **** ****Dim bWks As Worksheet, s As Shape, Alwayshidden As Range ********Set Alwayshidden = Range("i:l, X:AA")****** ' Define the range to be permanently hidden ****For Each bWks In ThisWorkbook.Worksheets ********If bWks.Name <> ComboBox1.Parent.Name Then**' don't hide the sheet with the combo box ************If InStr(1, bWks.Name, "Thicknr") Then ****************bWks.Visible = xlSheetVisible ************Else ****************bWks.Visible = xlSheetHidden ************End If ********End If ****Next bWks ****Select Case ComboBox2.Text '**** Case "1" through "3" are as below, but with successively less nested if loops ****** Case "4" ************Dim rngThickener4 As Range ************Set rngThickener4 = Range("AN:BF") ************ ************Application.ScreenUpdating = False ************************ ************For Each bWks In ThisWorkbook.Worksheets ****************If bWks.Name <> ComboBox1.Parent.Name Then**' don't hide the sheet with the combo box ******************** ********************If bWks.Name <> "Thicknr_1_Costing" Then ************************If bWks.Name <> "Thicknr_2_Costing" Then ****************************If bWks.Name <> "Thicknr_3_Costing" Then ********************************If bWks.Name <> "Thicknr_4_Costing" Then ********************************bWks.Visible = xlSheetHidden ********************************Else ********************************bWks.Visible = xlSheetVisible ********************************End If ****************************Else ****************************bWks.Visible = xlSheetVisible ****************************End If ************************Else ************************bWks.Visible = xlSheetVisible ************************End If ********************Else ********************bWks.Visible = xlSheetVisible ********************End If ****************End If ************Next bWks ************ ************Columns.Hidden = False ************rngThickener4.EntireColumn.Hidden = True ************Alwayshidden.EntireColumn.Hidden = True ' Hide the data validation selection columns ************Application.ScreenUpdating = True ********Case Else ******** Dim rngThickenerBlank As Range ************Set rngThickenerBlank = Range("R:BF") ****************** ************Application.ScreenUpdating = False ************For Each bWks In ThisWorkbook.Worksheets ****************If bWks.Name <> ComboBox1.Parent.Name Then**' don't hide the sheet with the combo box ******************** ********************If InStr(1, bWks.Name, "Thicknr_1") Then ************************bWks.Visible = xlSheetVisible ************************Else ************************bWks.Visible = xlSheetHidden ********************End If ****************End If ************ ************Next bWks ************ ************Columns.Hidden = False ************rngThickenerBlank.EntireColumn.Hidden = True ************Alwayshidden.EntireColumn.Hidden = True ' Hide the data validation selection columns ************Application.ScreenUpdating = True ************ ********End Select ******** End Sub 

Horrible - I know. Any suggestions on how to improve this (be kind, please!), as well as removing the Error 1004 I get in the situation described above?

Thanks

jindon · Nov 5, 2008

Can you just delete all the "*" as well as your comments in the code and specify the line that you have problem ?

PatrickW · Nov 5, 2008

Sorry about the *s - they didn't appear when I previewed my post. The problems occur in the ComboBox1_Change subroutine once box 2 has been changed, and I try and change box 1 again.

Oh, good grief. This is so embarrassing - I can't replicate the error. I've had it consistently for the last 2 days, and I've not done anything other than copy and paste it here, and now it works.

I'm really sorry for having wasted your time on this.

For your information, the lines that gave errors were:

Columns.Hidden = False

and this error just moved progressively lower in the code to everything else that referred to the .Hidden property, saying "Unable to get the hidden property of the range class."

Is there anything else you could suggest to clean up that horrible code? The sheets to be hidden/unhidden by combobox2 are named incrementally as "Thicknr_1_Costing", ...2_Costing and so on.

Thanks, and apologies.

jindon · Nov 5, 2008

OK

Just a couple of points
1) since your ComboBox is on the worksheet so ComboBox1.Parent.Name can be replaced with Me.Name

2) Try Select Case statement instead of long nested If statement

Rich (BB code):

Select Case bWks.Name 
  Case "Thicknr_1_Costing","Thicknr_2_Costing","Thicknr_3_Costing","Thicknr_4_Costing"
Then
      bWks.Visible = xlSheetHidden
  Case Else
      bWks.Visible = xlSheetVisible
End Select

Hiding columns and sheets based on combo-box values

PatrickW

New Member

Excel Facts

PatrickW

New Member

PatrickW

New Member

jindon

MrExcel MVP

PatrickW

New Member

jindon

MrExcel MVP

PatrickW

New Member

jindon

MrExcel MVP

PatrickW

New Member

jindon

MrExcel MVP

Similar threads

Forum statistics

Share this page

Hiding columns and sheets based on combo-box values

New Member

Excel Facts

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock