Solved: Find Then Unhide Help

[Designer6]

Hi
I am having a problem with this piece of code even after i set the LookIn:=xlFormulas and removed LookAfter. Basically, first the program would hide the data within a selected range then unhide the column that match the caption. The code has no problem when i don't hide the data. But when i hide the data It gives me this error Run-time error '91' Object variable or with block variable not set. Could anyone help me out Below is the code

Private Sub CommandButton1_Click() 'This is for the "OK" button
'Declare variables to hold the value of caption

Dim Sheet As String

'Assign variables, Declare active sheet and range
Sheet = "General"
Worksheets(Sheet).Select
' Hide all data within range
Range("P10:AV254").EntireColumn.Hidden = True

' Begin your Selection
If CheckBox1.Value = True Then
    Range("P10:AV254").Select
    Cells.Find(What:=CheckBox1.Caption, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).EntireColumn.Select
    
    'Want to unhide a particular column base on
    Columns(ActiveCell.Column).EntireColumn.Hidden = False
        
End If

End Sub

[Bob Phillips]

This works for me

[vba]

Private Sub CommandButton1_Click() 'This is for the "OK" button
'Declare variables to hold the value of caption
Dim Sheet As String
Dim rng As Range
Dim cell As Range

'Assign variables, Declare active sheet and range
With Worksheets("General")

Set rng = .Range("P10:AV254")

' Hide all data within range
rng.EntireColumn.Hidden = True

' Begin your Selection
If CheckBox1.Value = True Then
Set cell = rng.Find(What:=CheckBox1.cation, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not cell Is Nothing Then

'Want to unhide a particular column base on
cell.EntireColumn.Hidden = False
End If
End If
End With
End Sub
[/vba]

[Designer6]

Thanks buddy. It works great. Let's say i would like to have the same functionality for more checkboxes like 30 or so. But this time i would like let's say checkbox1, 2, 7, 8 columns to unhide at the sametime when i select them do i just copy and paste the code you gave me ?


Thanks

Designer6

[Bob Phillips]

I'd do it a bit smarter than that

[vba]

Private Sub CommandButton1_Click() 'This is for the "OK" button
'Declare variables to hold the value of caption
Dim Sheet As String
Dim rng As Range

'Assign variables, Declare active sheet and range
With Worksheets("General")

Set rng = .Range("P10:AV254")

' Hide all data within range
rng.EntireColumn.Hidden = True

Call FindAndUnhide(CheckBox1, rng)
Call FindAndUnhide(CheckBox2, rng)
Call FindAndUnhide(CheckBox7, rng)
Call FindAndUnhide(CheckBox8, rng)

End With
End Sub

Private Sub FindAndUnhide(ByRef cb As msforms.CheckBox, ByRef rng As Range)
Dim cell As Range

' Begin your Selection
If cb.Value = True Then
Set cell = rng.Find(What:=cb.Caption, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not cell Is Nothing Then

'Want to unhide a particular column base on
cell.EntireColumn.Hidden = False
End If
End If
End Sub
[/vba]

[Designer6]

I am no expert. I like programing, i am an ASM, C/C++ programer (firmware). But VB for Application is a brand new, 2-3 months language to me. I would never thought of doing it the way you have the code. From what it looks like you are an expert in VB. Many Thanks to you buddy

[Designer6]

Hello Buddy,


I tested your code. It works fine to unhide individual column with respect to individual checkbox caption. However here is a little problem. Let's say, when I select multiple checkboxes at the same time, like checkbox1, checkbox2 and Checkbox7. It won't unhide all three columns at the same time. It only unhides the column of checkbox1 caption. I’ve been debugging the code for more than 2 hrs but could get it to display all at the same time. Do you have any recommendations?

I have Excel 2003.

What i am thinking might have caused the problem is the range fails after the first checkbox unhide ?



Many Thanks
Designer6

[Bob Phillips]

Works fine for me. ANy combination of the checked boxes gets the appropriate column unhid.

In my tests I used the same range all the time applied to all checkboxes, "P10:AV254". Is that a correct assumption, do they all apply to the same range?

[Designer6]

Hello Xld
Yes, it works fine to get individual column to unhide However, i can't get checkbox1, checkbox2, checkbox7, and checkbox8 columns to unhide at the same time ? Attached is my project, if you don't mind could you take a look and tell me what i need to do to get let's say checkbox 1, 2, 7 8 or in any other combinations to work at the same time.


Many Thanks,
Designer6

[Designer6]

Hello XLD,

Thanks a lot for you help i figured out what i did wrong one very dumb a** mistake

[Bob Phillips]

Can you give a quick run-down of a test scenario? What boxes on the form to select, and wic columns do they pertian to?

[Designer6]

The Main Page sheet is where people going to make the role selection. Basically Checkbox1 caption pertaining to column P, checkbox2 caption pertaining to column Q. Checkbox7 pertaining to column label PM or V etc... The code not going to work for all checkboxes. This is caption base.

So after i unhide a particular column. My next task is to delete rows base on empty cells using the same range. Personally i think this next task is challenging. Let's say colum P, Q and V is unhided. If the first row (row 11) of column P, Q and V cells has any data don't delete that row go to the next row if the next row doesn't have any data in them delete that row. If the next 10 rows doesn't have any data delete all 10 rows. I have to do that to row 254. Many thanks to you XLD

Best Regards,
Designer6