Hide Rows based on Combobox Selection

[Katie]

I have a spreadsheet that I am automating. I have 15 lines with 15 comboboxes
each are labeled uniquely and based on the selection I need it to hide rows
on multiple sheets.

Here is what I have for one combobox, they are all very similar:

Private Sub ComboBox2XR_Change()
Dim c As String
Dim d As String


c = _
Worksheets("ReportRequestXR").Cells.Find(What:="ActXR2" & i &
"_BEGIN", after:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Row
d = _
Worksheets("ReportRequestXR").Cells.Find(What:="ACTXR2" & i &
"_END", after:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Row

If ComboBox2XR.Value = "" Then
ComboBox2XR.Enabled = True
ComboBox2XR.Visible = True

End If

If ComboBox2XR.Value = "Encounter-Level" Then
ComboBox2XR.Enabled = True
Worksheets("ReportRequestXR").Rows(c & ":" & d).Hidden = xlVeryHidden

ElseIf ComboBox2XR.Value = "Accession-Level" Then
ComboBox2XR.Enabled = True
Worksheets("ReportRequestXR").Rows(c & ":" & d).Visible = True

End If

End Sub

The rows are not hiding as planned, any ideas?

 

[joel]

Leave out the Begin prtion of both the find methods. Putting A1 at th
Begin item will skip A1 and look at cell A1 as the last cell in th
sheet rather than the first cell. If your seatch data is in cell a
then you may not find what you are expecting.

 

[Mishell]

Hi Katie.

Try like this. It should work.

Private Sub ComboBox2XR_Change()
Dim c As Long
Dim d As Long
Dim result As Range

Worksheets("ReportRequestXR").Select
Worksheets("ReportRequestXR").Cells(1, 1).Select

Set result = _
Worksheets("ReportRequestXR").Cells.Find(What:="ActXR2" & i &
"_BEGIN", after:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False)

If Not result Is Nothing Then
c = result.Row
End If

Set result = _
Worksheets("ReportRequestXR").Cells.Find(What:="ACTXR2" & i &
"_END", after:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False)

If Not result Is Nothing Then
d = result.Row
End If

If Not c > 0 And d > 0 Then
MsgBox "Not found"
Exit Sub
End If

If ComboBox2XR.Value = "" Then
ComboBox2XR.Enabled = True
ComboBox2XR.Visible = True

End If

If ComboBox2XR.Value = "Encounter-Level" Then
ComboBox2XR.Enabled = True
Worksheets("ReportRequestXR").Rows(c & ":" & d).Hidden = True

ElseIf ComboBox2XR.Value = "Accession-Level" Then
ComboBox2XR.Enabled = True
Worksheets("ReportRequestXR").Rows(c & ":" & d).Hidden = False

End If

End Sub


Mishell

 

[Ryan H]

Is this combobox in a userform? How is your variable i evaluated? I will
assume it is a whole number so I declared it as a Long datatype. Make sure
you declare all your variables in the future, like I did. I assumed this
combobox is located in a userform. I modified your code to prevent errors.
If you get an error please indicate what line the error is on and what the
error description is so we can help you. Try this code.

Private Sub ComboBox2XR_Change()

Dim MyString1 As String
Dim Range1 As Range
Dim c As Long
Dim MyString2 As String
Dim Range2 As Range
Dim d As Long
Dim i As Long

MyString1 = "ACTXR2" & i & "_BEGIN"
MyString2 = "ACTXR2" & i & "_END"

With Sheets("ReportRequestXR")
Set Range1 = .Cells.Find(What:=MyString1, _
After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set Range2 = .Cells.Find(What:=MyString2, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If Not Range1 Is Nothing Then
c = Range1.Row
Else
MsgBox "Can't find " & MyString1
Exit Sub
End If

If Not Range2 Is Nothing Then
d = Range2.Row
Else
MsgBox "Can't find " & MyString2
Exit Sub
End If

Select Case ComboBox2XR.Value
Case Is = ""
ComboBox2XR.Enabled = True
ComboBox2XR.Visible = True

Case Is = "Encounter-Level"
ComboBox2XR.Enabled = True
Sheets("ReportRequestXR").Rows(c & ":" & d).Hidden = True

Case Is = "Accession-Level"
ComboBox2XR.Enabled = True
Sheets("ReportRequestXR").Rows(c & ":" & d).Hidden = True
End Select
End Sub

 

[Ryan H]

It's ok to leave After:=Range("A1"). This just means that the search will
begin with A2 and A1 will be the last cell to be searched. If you omit the
After:= argument the Find method will search the first cell in the range to
be searched, in this case it would be A1. My point is, either way if the
text Katie is searching for will be found if it is in the worksheet. Just
wanted to let you know.

 

[Ryan H]

Two things.

1.) You really don't have to select the worksheet first. So you can omit

Worksheets("ReportRequestXR").Select
Worksheets("ReportRequestXR").Cells(1, 1).Select

from you code.

2.) Your If...Then statement will not work properly. Both c and d will
need to me positive integers. Your If...Then statement (If Not c > 0 And d >
0 Then) only ensures that c is a positive integer. It should be written like

If Not c > 0 Or Not d > 0 Then

or better,

If c = 0 Or d = 0 Then

This lines will ensure c or d are not 0. If any c or d are 0 or less Excel
will throw an error.

Hope this helps! If so, let me know, click "YES" below.

 

[joel]

[ryan: think about your response again! the original posting said

The rows are not hiding as planned, an
ideas?

If you skip the 1st begin what will happen?

row Number

1 Begin
2
3
4
5 End
6
7 Begin
8
9
10 End
11

 

[Mishell]

Your are right. The condition is (c > 0 And d > 0).
I forgot the parentheses. It should be

If Not (c > 0 And d > 0) Then
MsgBox "Not found"
End If

or ( which is the same thing)

If (c > 0 And d > 0) = False Then
MsgBox "Not found"
End If

Regards.
Mishell

 

[Katie]

Hi Ryan,

My combobox is not on a userform, I wasn't sure it would accomplish what I
want. The combobox is on another spreadsheet (within the workbook) there are
a total of 15 so the person can identify 15 different items and say what type
they are. There are only two types Encounter and Accession. If they select
encounter I want to hide all of the accession steps which are labeled off to
the side by ActXR1, ActXR2 etc. on a different worksheet. I have similar
steps for other areas of the workbook that work fine however they do not have
any comboboxes. I created a sheet that has the combobox selections and
assigned within the boxes themselves. I wasn't sure if I needed to delare
them again. Please let me know if this makes more sense or if you have any
additional ideas I am willing to try.
Thanks for your help,
Katie

 

[Ryan H]

Put this code in the worksheet module that contains the ComboBox2XR. Since
you didn't specify if the Combobox is an ActiveX or Forms control, I assumed
it is an ActiveX combobox. This code will fire everytime you change the
combobox box.

If this code doesn't work right, you will have to specify exactly what is
not working for you. Are there any errors, if so, what line of code is
throwing the error and what is the error description.

By the way, why do you have a variable i in the strings you are looking for?
Is the variable i assigned a value in another sub?

Hope this helps! If so, let me know, click "YES" below.

Private Sub ComboBox2XR_Change()

Dim MyString1 As String
Dim Range1 As Range
Dim c As Long
Dim MyString2 As String
Dim Range2 As Range
Dim d As Long
Dim i As Long

MyString1 = "ACTXR2" & i & "_BEGIN"
MyString2 = "ACTXR2" & i & "_END"

With Sheets("ReportRequestXR")
Set Range1 = .Cells.Find(What:=MyString1, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set Range2 = .Cells.Find(What:=MyString2, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If Not Range1 Is Nothing Then
c = Range1.Row
Else
MsgBox "Can't find " & MyString1
Exit Sub
End If

If Not Range2 Is Nothing Then
d = Range2.Row
Else
MsgBox "Can't find " & MyString2
Exit Sub
End If

Select Case ComboBox2XR.Value
Case Is = ""
ComboBox2XR.Enabled = True
ComboBox2XR.Visible = True

Case Is = "Encounter-Level"
ComboBox2XR.Enabled = True
Sheets("ReportRequestXR").Rows(c & ":" & d).Hidden = True

Case Is = "Accession-Level"
ComboBox2XR.Enabled = True
Sheets("ReportRequestXR").Rows(c & ":" & d).Hidden = True
End Select

End Sub

 

[Ryan H]

Yes, you are right, but I was under the impression she is looking for unique
values.

 

[Katie]

Ryan,

The ComboBox is ActiveX combobox. When I run through the code by selecting
the Encounter-Level option on the spreadsheet and complete the additional
macros I do not receive an error. I did go into the code itself and pressed
F8 to run through the scripts and I received an error then Run-Time error 91:
Object variable or With block variable not set once it hit the select case
steps.

I took the "i" out of the scripts as I had copied the line from another
portion of the test scripts and the "i" was supposed to be the number ie
actxr2 (it would be the 2).
I also named ranges for each of the accession steps. So on one sheet the
user can select up to 15 items and define if they are accession or encounter.
From there another sheet a group of steps that enclude accession steps so
that information is on columns A and the labels are from F:H. I identified
where the steps begin and where they end; however as I stated before I also
named those ranges but I still cannot get them to hide the steps. I have
tested by selecting both accession and encounter.

This is the code I copied over:
Private Sub ComboBox2XR_Change()

Dim MyString1 As String
Dim Range1 As Range
Dim c As Long
Dim MyString2 As String
Dim Range2 As Range
Dim d As Long
Dim i As Long
Dim combobox2XR As combobox

MyString1 = "ACTXR2_BEGIN"
MyString2 = "ACTXR2_END"



With Sheets("ReportRequestXR")
Set Range1 = .Cells.Find(What:=MyString1, _
After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set Range2 = .Cells.Find(What:=MyString2, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If Not Range1 Is Nothing Then
c = Range1.Row
Else
MsgBox "Can't find " & MyString1
Exit Sub
End If

If Not Range2 Is Nothing Then
d = Range2.Row
Else
MsgBox "Can't find " & MyString2
Exit Sub
End If

Select Case combobox2XR.Value
Case Is = ""
combobox2XR.Enabled = True
combobox2XR.Visible = True

Case Is = "Encounter-Level"
combobox2XR.Enabled = True
Sheets("ReportRequestXR").Rows(c & ":" & d).Hidden = True

Case Is = "Accession-Level"
combobox2XR.Enabled = True
Sheets("ReportRequestXR").Rows(c & ":" & d).Hidden = True
End Select
End Sub

 

[Ryan H]

You have to reference the sheet ComboBox2XR is in. I changed the code a
little. All you have to do is replace "YOUR SHEET NAME HERE" with the sheet
name that contains ComboBox2XR.

Hope this helps! If so, let me know, click "YES" below.Private Sub

Private Sub ComboBox2XR_Change()

Dim MyString1 As String
Dim Range1 As Range
Dim c As Long
Dim MyString2 As String
Dim Range2 As Range
Dim d As Long

MyString1 = "ACTXR2_BEGIN"
MyString2 = "ACTXR2_END"

With Sheets("ReportRequestXR")
Set Range1 = .Cells.Find(What:=MyString1, _
After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set Range2 = .Cells.Find(What:=MyString2, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If Not Range1 Is Nothing Then
c = Range1.Row
Else
MsgBox "Can't find " & MyString1
Exit Sub
End If

If Not Range2 Is Nothing Then
d = Range2.Row
Else
MsgBox "Can't find " & MyString2
Exit Sub
End If

With Sheets("YOUR SHEET NAME HERE").ComboBox2XR
Select Case .Value
Case Is = ""
.Enabled = True
.Visible = True

Case Is = "Encounter-Level"
.Enabled = True
Sheets("ReportRequestXR").Rows(c & ":" & d).Hidden = True

Case Is = "Accession-Level"
.Enabled = True
Sheets("ReportRequestXR").Rows(c & ":" & d).Hidden = True
End Select
End With

End Sub

 

[Katie]

Ryan,

I did what you requested but it is still not hiding the rows. Here is what I
have:
Private Sub ComboBox2XR_Change()

Dim MyString1 As String
Dim Range1 As Range
Dim c As Long
Dim MyString2 As String
Dim Range2 As Range
Dim d As Long
Dim ComboBox2XR As combobox

MyString1 = "ACTXR2_BEGIN"
MyString2 = "ACTXR2_END"



With Sheets("ReportRequestXR")
Set Range1 = .Cells.Find(What:=MyString1, _
After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set Range2 = .Cells.Find(What:=MyString2, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If Not Range1 Is Nothing Then
c = Range1.Row
Else
MsgBox "Can't find " & MyString1
Exit Sub
End If

If Not Range2 Is Nothing Then
d = Range2.Row
Else
MsgBox "Can't find " & MyString2
Exit Sub
End If

With Sheets("ClinicalViewXR").ComboBox2XR
Select Case ComboBox2XR.Value
Case Is = ""
ComboBox2XR.Enabled = True
ComboBox2XR.Visible = True

Case Is = "Encounter-Level"
ComboBox2XR.Enabled = True
Sheets("ReportRequestXR").Rows(c & ":" & d).Hidden = True

Case Is = "Accession-Level"
ComboBox2XR.Enabled = True
Sheets("ReportRequestXR").Rows(c & ":" & d).Hidden = True
End Select
End With
End Sub

The frustrating part is that I have another section of code that hides the
rows correctly but they are not using comboboxes.

 

[Ryan H]

Something is missing here and I'm not sure where your problems are. I was
able to get the code to work for me just fine. Can you send me a copy of
your workbook? I could then diagnos exactly what is happening.

send workbook to: (e-mail address removed)

 

[Katie]

Ryan,

Thank you, I finally got everything working. I did run into an issue with
the routines not running but figured out I had it on the wrong place (in
Microsoft Excel Objects versus a module). I did have to modify just a bit but
your code really helped. I appreciate your assistance!

Private Sub ComboBox2XR_Change()

Dim MyString1 As String
Dim Range1 As Range
Dim c As Long
Dim MyString2 As String
Dim Range2 As Range
Dim d As Long


MyString1 = "ACTXR2_BEGIN"
MyString2 = "ACTXR2_END"



With Sheets("ReportRequestXR")
Set Range1 = .Cells.Find(What:=MyString1, _
After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set Range2 = .Cells.Find(What:=MyString2, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If Not Range1 Is Nothing Then
c = Range1.Row
Else
MsgBox "Can't find " & MyString1
Exit Sub
End If

If Not Range2 Is Nothing Then
d = Range2.Row
Else
MsgBox "Can't find " & MyString2
Exit Sub
End If

With Sheets("ClinicalViewXR").combobox2XR
Select Case .Value


Case Is = "Encounter-Level"
.Enabled = True
Sheets("ReportRequestXR").Rows(c & ":" & d).Hidden = True

Case Is = "Accession-Level"
.Enabled = True
Sheets("ReportRequestXR").Rows(c & ":" & d).Hidden = False
End Select
End With
End Sub

 

[Ryan H]

Glad you got it working. If you found my postings helpful please click "YES"
below.

Let me know if you have other questions.