Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation If statement moved to userform
I currently have a worksheet that uses a Data validation List called
"counties". When the user selects a "county" the following code runs on the sheet. '(Target.Row/Column is C3, on countyselect sheet) Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 3 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Worksheets("Places").Range("G2").Calculate Worksheets("Places").Range("PlacesDatabase") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Places").Range("G1:G2"), _ CopyToRange:=Range("B40:C40"), Unique:=False End If End Sub I used the code from one of contexures.com files. It populates the current sheet at location B40 with information from another sheet called ("Places") Based on which county the users selected. My Goal is to put this onto the userform I have in the workbook. Thus far I have created the combobox on the userform, and I used the Rowsource properties to populate the combobox. Counties!A1:A25 (a sheet with the listings). I have moved part of the code over to the userform combobox, but of course nothing happens for me. I don't program alot in vba, but use examples of code to achive my task. How do I get the combobox to do the same thing that is taking place on the worksheet when "C3" is changed. Private Sub cboCountySelect_Change() End Sub Thanks very much, I appreciate your efforts in helping me. thom |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation If statement moved to userform
Untested
Private Sub Combobox1_Click() Worksheets("Places").Range("G2").Calculate Worksheets("Places").Range("PlacesDatabase") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Places").Range("G1:G2"), _ CopyToRange:=Range("B40:C40"), Unique:=False End Sub but I don't see where cell C3 comes into play? -- HTH Bob Phillips "thom hoyle" wrote in message ... I currently have a worksheet that uses a Data validation List called "counties". When the user selects a "county" the following code runs on the sheet. '(Target.Row/Column is C3, on countyselect sheet) Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 3 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Worksheets("Places").Range("G2").Calculate Worksheets("Places").Range("PlacesDatabase") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Places").Range("G1:G2"), _ CopyToRange:=Range("B40:C40"), Unique:=False End If End Sub I used the code from one of contexures.com files. It populates the current sheet at location B40 with information from another sheet called ("Places") Based on which county the users selected. My Goal is to put this onto the userform I have in the workbook. Thus far I have created the combobox on the userform, and I used the Rowsource properties to populate the combobox. Counties!A1:A25 (a sheet with the listings). I have moved part of the code over to the userform combobox, but of course nothing happens for me. I don't program alot in vba, but use examples of code to achive my task. How do I get the combobox to do the same thing that is taking place on the worksheet when "C3" is changed. Private Sub cboCountySelect_Change() End Sub Thanks very much, I appreciate your efforts in helping me. thom |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation If statement moved to userform
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation If statement moved to userform
Since everything appears to be based on having the value in C3 of the sheet
CountySelect, then you need to put it there. Private Sub Combobox1_Click() Worksheets("CountySelect").Range("C3").Value = Combobox1.Value Worksheets("Places").Range("G2").Calculate Worksheets("Places").Range("PlacesDatabase") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Places").Range("G1:G2"), _ CopyToRange:=Range("B40:C40"), Unique:=False End Sub Otherwise you need to change you approach, such as putting it directly in G2 Private Sub Combobox1_Click() Worksheets("Places").Range("G2").Value = Combobox1.Value Worksheets("Places").Range("PlacesDatabase") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Places").Range("G1:G2"), _ CopyToRange:=Range("B40:C40"), Unique:=False End Sub -- Regards, Tom Ogilvy "thom hoyle" wrote in message ... Hi Bob. I tried you code, nothing seems to be happening. would you mind if I sent you the file? U may email me At I will reply with file. thanks "Bob Phillips" wrote: Untested Private Sub Combobox1_Click() Worksheets("Places").Range("G2").Calculate Worksheets("Places").Range("PlacesDatabase") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Places").Range("G1:G2"), _ CopyToRange:=Range("B40:C40"), Unique:=False End Sub but I don't see where cell C3 comes into play? -- HTH Bob Phillips "thom hoyle" wrote in message ... I currently have a worksheet that uses a Data validation List called "counties". When the user selects a "county" the following code runs on the sheet. '(Target.Row/Column is C3, on countyselect sheet) Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 3 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Worksheets("Places").Range("G2").Calculate Worksheets("Places").Range("PlacesDatabase") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Places").Range("G1:G2"), _ CopyToRange:=Range("B40:C40"), Unique:=False End If End Sub I used the code from one of contexures.com files. It populates the current sheet at location B40 with information from another sheet called ("Places") Based on which county the users selected. My Goal is to put this onto the userform I have in the workbook. Thus far I have created the combobox on the userform, and I used the Rowsource properties to populate the combobox. Counties!A1:A25 (a sheet with the listings). I have moved part of the code over to the userform combobox, but of course nothing happens for me. I don't program alot in vba, but use examples of code to achive my task. How do I get the combobox to do the same thing that is taking place on the worksheet when "C3" is changed. Private Sub cboCountySelect_Change() End Sub Thanks very much, I appreciate your efforts in helping me. thom |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation If statement moved to userform
As I said, I failed to see where C3 comes into it. You don't use it at all
in the code you gave. You could simply set it to the selection from the Combo (Worksheets("Places").Range("C3").Value = Combobox1.Value), but it doesn't seem to drive anything. What do you do with it? -- HTH Bob Phillips "thom hoyle" wrote in message ... Hi Bob. I tried you code, nothing seems to be happening. would you mind if I sent you the file? U may email me At I will reply with file. thanks "Bob Phillips" wrote: Untested Private Sub Combobox1_Click() Worksheets("Places").Range("G2").Calculate Worksheets("Places").Range("PlacesDatabase") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Places").Range("G1:G2"), _ CopyToRange:=Range("B40:C40"), Unique:=False End Sub but I don't see where cell C3 comes into play? -- HTH Bob Phillips "thom hoyle" wrote in message ... I currently have a worksheet that uses a Data validation List called "counties". When the user selects a "county" the following code runs on the sheet. '(Target.Row/Column is C3, on countyselect sheet) Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 3 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Worksheets("Places").Range("G2").Calculate Worksheets("Places").Range("PlacesDatabase") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Places").Range("G1:G2"), _ CopyToRange:=Range("B40:C40"), Unique:=False End If End Sub I used the code from one of contexures.com files. It populates the current sheet at location B40 with information from another sheet called ("Places") Based on which county the users selected. My Goal is to put this onto the userform I have in the workbook. Thus far I have created the combobox on the userform, and I used the Rowsource properties to populate the combobox. Counties!A1:A25 (a sheet with the listings). I have moved part of the code over to the userform combobox, but of course nothing happens for me. I don't program alot in vba, but use examples of code to achive my task. How do I get the combobox to do the same thing that is taking place on the worksheet when "C3" is changed. Private Sub cboCountySelect_Change() End Sub Thanks very much, I appreciate your efforts in helping me. thom |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation If statement moved to userform
That worked great. Excellent.. Thanks for the reply so quickly.
thomas "Tom Ogilvy" wrote: Since everything appears to be based on having the value in C3 of the sheet CountySelect, then you need to put it there. Private Sub Combobox1_Click() Worksheets("CountySelect").Range("C3").Value = Combobox1.Value Worksheets("Places").Range("G2").Calculate Worksheets("Places").Range("PlacesDatabase") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Places").Range("G1:G2"), _ CopyToRange:=Range("B40:C40"), Unique:=False End Sub Otherwise you need to change you approach, such as putting it directly in G2 Private Sub Combobox1_Click() Worksheets("Places").Range("G2").Value = Combobox1.Value Worksheets("Places").Range("PlacesDatabase") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Places").Range("G1:G2"), _ CopyToRange:=Range("B40:C40"), Unique:=False End Sub -- Regards, Tom Ogilvy "thom hoyle" wrote in message ... Hi Bob. I tried you code, nothing seems to be happening. would you mind if I sent you the file? U may email me At I will reply with file. thanks "Bob Phillips" wrote: Untested Private Sub Combobox1_Click() Worksheets("Places").Range("G2").Calculate Worksheets("Places").Range("PlacesDatabase") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Places").Range("G1:G2"), _ CopyToRange:=Range("B40:C40"), Unique:=False End Sub but I don't see where cell C3 comes into play? -- HTH Bob Phillips "thom hoyle" wrote in message ... I currently have a worksheet that uses a Data validation List called "counties". When the user selects a "county" the following code runs on the sheet. '(Target.Row/Column is C3, on countyselect sheet) Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 3 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Worksheets("Places").Range("G2").Calculate Worksheets("Places").Range("PlacesDatabase") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Places").Range("G1:G2"), _ CopyToRange:=Range("B40:C40"), Unique:=False End If End Sub I used the code from one of contexures.com files. It populates the current sheet at location B40 with information from another sheet called ("Places") Based on which county the users selected. My Goal is to put this onto the userform I have in the workbook. Thus far I have created the combobox on the userform, and I used the Rowsource properties to populate the combobox. Counties!A1:A25 (a sheet with the listings). I have moved part of the code over to the userform combobox, but of course nothing happens for me. I don't program alot in vba, but use examples of code to achive my task. How do I get the combobox to do the same thing that is taking place on the worksheet when "C3" is changed. Private Sub cboCountySelect_Change() End Sub Thanks very much, I appreciate your efforts in helping me. thom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation w/an If Statement | Excel Worksheet Functions | |||
Data Validation w/If Statement | Excel Worksheet Functions | |||
Data Validation Cell - Move to UserForm | Excel Worksheet Functions | |||
Using a Userform instead of Data Validation | Excel Programming | |||
using the if statement with data validation | Excel Programming |