| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Rows based on Combobox Selection
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide Rows based on Combobox Selection
Leave out the Begin prtion of both the find methods. Putting A1 at the Begin item will skip A1 and look at cell A1 as the last cell in the sheet rather than the first cell. If your seatch data is in cell a1 then you may not find what you are expecting. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=183105 Microsoft Office Help |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide Rows based on Combobox Selection
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide Rows based on Combobox Selection
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 -- Cheers, Ryan "Katie" wrote: 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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide Rows based on Combobox Selection
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. -- Cheers, Ryan "joel" wrote: Leave out the Begin prtion of both the find methods. Putting A1 at the Begin item will skip A1 and look at cell A1 as the last cell in the sheet rather than the first cell. If your seatch data is in cell a1 then you may not find what you are expecting. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=183105 Microsoft Office Help . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide Rows based on Combobox Selection
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? . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide Rows based on Combobox Selection
[ryan: think about your response again! the original posting said The rows are not hiding as planned, any 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 -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=183105 Microsoft Office Help |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide Rows based on Combobox Selection
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" wrote: 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 -- Cheers, Ryan "Katie" wrote: 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? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide Rows based on Combobox Selection
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 -- Cheers, Ryan "Katie" wrote: 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" wrote: 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 -- Cheers, Ryan "Katie" wrote: 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? |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide Rows based on Combobox Selection
Yes, you are right, but I was under the impression she is looking for unique
values. -- Cheers, Ryan "joel" wrote: [ryan: think about your response again! the original posting said The rows are not hiding as planned, any 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 -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=183105 Microsoft Office Help . |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide Rows based on Combobox Selection
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:D 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" wrote: 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 -- Cheers, Ryan "Katie" wrote: 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" wrote: 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 -- Cheers, Ryan "Katie" wrote: 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? |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide Rows based on Combobox Selection
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. 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:D 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" wrote: 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 -- Cheers, Ryan "Katie" wrote: 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" wrote: 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 -- Cheers, Ryan "Katie" wrote: 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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide Rows based on Combobox Selection
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. 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:D 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" wrote: 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 -- Cheers, Ryan "Katie" wrote: 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" wrote: 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 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide Rows based on Combobox Selection
|
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide Rows based on Combobox Selection
|
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide Rows based on Combobox Selection
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. 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:D 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 |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hide Rows based on Combobox Selection
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Call a UserForm based on ComboBox selection | Excel Programming | |||
| Populate Sheet based on ComboBox selection | Excel Programming | |||
| How to populate a combobox based on selection from another combobo | Excel Programming | |||
| VB Script based on combobox selection | Excel Programming | |||
| Hide Rows based on Time Selection in timesheet. | Excel Programming | |||
Linear Mode
