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.


--
Cheers,
Ryan


"Mishell" wrote:

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:="Ac tXR2" & 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:="AC TXR2" & 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

"Katie" a écrit dans le message de news:
...
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:="Ac tXR2" & i &
"_BEGIN", after:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Row
d = _
Worksheets("ReportRequestXR").Cells.Find(What:="AC TXR2" & 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?


.