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

Let me know if you have other questions.
--
Cheers,
Ryan


"Katie" wrote:

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" wrote:

After looking at what you said is your current code I noticed that you didn't
copy my code. Cut and Paste this code. If this doesn't work you can e-mail
me a copy of the workbook at and I can fix it.

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 = ""
.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

--
Cheers,
Ryan


"Katie" wrote:

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" wrote:

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
--
Cheers,
Ryan


"Katie" wrote:

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.