Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting input from combobox to update sheet
In a userform I'm have a Combobox (CB) that holds names. When the routine is
first run list for the CB is empty. The user then inputs a name and this is to be stored for subsequent uses of the workbook. Any other time the form is opened, the ListIndex = 0 will show the user's name for confirmation. The following is another option that I'm contemplating but I need to get the single entry done first: "If another user needs to, they can input their name and it is then added to the top of the list, so that the next time the form is opened this new entry will be the default name shown on the form and the previous entries would show in the dropdown as secondary names." It is my understanding that manual input into a combobox can be stored back to a sheet, preferably the list for the combobox. Can't seem to find a way to start this that works. I tried using CB_Exit to allow for input but it doesn't copy the input back to the named range on the sheet. Also, I don't seem to getting the new entry into the value property after it's typed in. -------------------------------------------------- ' CBName is the combobox and Cardholder is the named range Private Sub CBName_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim CHName As String CHName = Sheets("Carddata").Range("Cardholder").Cells(1, 1).Value If CHName = "" Then CHName = Me.CBName.Value End If End Sub ---------------------------------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting input from combobox to update sheet
Use the Before Update event of the control to store data; like the below. Try and feedback.. Private Sub CBName_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) If WorksheetFunction.CountIf(Sheets("Carddata"). _ Range("A:A"), ComboBox1.Text) = 0 Then lngRow = Sheets("Carddata").Cells(Rows.Count, "A").End(xlUp).Row + 1 Sheets("Carddata").Range("A" & lngRow) = CBName.Text End If End Sub If this post helps click Yes --------------- Jacob Skaria "John G." wrote: In a userform I'm have a Combobox (CB) that holds names. When the routine is first run list for the CB is empty. The user then inputs a name and this is to be stored for subsequent uses of the workbook. Any other time the form is opened, the ListIndex = 0 will show the user's name for confirmation. The following is another option that I'm contemplating but I need to get the single entry done first: "If another user needs to, they can input their name and it is then added to the top of the list, so that the next time the form is opened this new entry will be the default name shown on the form and the previous entries would show in the dropdown as secondary names." It is my understanding that manual input into a combobox can be stored back to a sheet, preferably the list for the combobox. Can't seem to find a way to start this that works. I tried using CB_Exit to allow for input but it doesn't copy the input back to the named range on the sheet. Also, I don't seem to getting the new entry into the value property after it's typed in. -------------------------------------------------- ' CBName is the combobox and Cardholder is the named range Private Sub CBName_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim CHName As String CHName = Sheets("Carddata").Range("Cardholder").Cells(1, 1).Value If CHName = "" Then CHName = Me.CBName.Value End If End Sub ---------------------------------------------------- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting input from combobox to update sheet
You've got a bit of a misconception of how assignments work. You Dim'ed CHName as a string and then assigned the value of that string to the value of a cell: CHName = Sheets("Carddata").Range("Cardholder").Cells(1, 1).Value later you conditionally assigned a new value to CHName CHName = Me.CBName.Value which overwrote the original assignment of the CHName - but heres's the significant part - just because the original assignment of CHName's value was to the value of a cell in the Cardholder named range, VBA doesn't back-propagate a change in CHName's value to the cell value in the named range. To accomplish that, you would need something like Sheets("Carddata").Range("Cardholder").Cells(1).Va lue = Me.CBName.Value As an aside, note that I replaced your .Cells(1,1) with .Cells(1). If your names are to be stored in a single column named range Cardholder, you can either use the .Cells(1) or Cells(1,1) notation, BUT the second name in the named range would need to have a reference of .Cells(2) or ..Cells(2,1). Since the ,1 isn't doing anything for you (except perhaps confusing you should you try to use .Cells(1,2) to get the value of the second name in the Cardholder named range) I'd recommend the simpler notation. Also, note that if you want the most-recently entered name to be the first choice, you are going to have to do some management of the names in the Cardholder named range - that is, you are going to have to shift any other names already in the Cardholder named range down before adding the newest one to .Cells(1) - unless you work out some way to load the names into the combobox from the bottom of the Cardholder named range up to the top - in which case the newest name could be at the bottom of the Cardholder range. Hope this helps some... -- jamescox ------------------------------------------------------------------------ jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112817 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting input from combobox to update sheet
Private Sub CBName_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) If WorksheetFunction.CountIf(Sheets("Carddata"). _ Range("A:A"), CBName.Text) = 0 Then lngRow = Sheets("Carddata").Cells(Rows.Count, "A").End(xlUp).Row + 1 Sheets("Carddata").Range("A" & lngRow) = CBName.Text End If End Sub If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Use the Before Update event of the control to store data; like the below. Try and feedback.. Private Sub CBName_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) If WorksheetFunction.CountIf(Sheets("Carddata"). _ Range("A:A"), ComboBox1.Text) = 0 Then lngRow = Sheets("Carddata").Cells(Rows.Count, "A").End(xlUp).Row + 1 Sheets("Carddata").Range("A" & lngRow) = CBName.Text End If End Sub If this post helps click Yes --------------- Jacob Skaria "John G." wrote: In a userform I'm have a Combobox (CB) that holds names. When the routine is first run list for the CB is empty. The user then inputs a name and this is to be stored for subsequent uses of the workbook. Any other time the form is opened, the ListIndex = 0 will show the user's name for confirmation. The following is another option that I'm contemplating but I need to get the single entry done first: "If another user needs to, they can input their name and it is then added to the top of the list, so that the next time the form is opened this new entry will be the default name shown on the form and the previous entries would show in the dropdown as secondary names." It is my understanding that manual input into a combobox can be stored back to a sheet, preferably the list for the combobox. Can't seem to find a way to start this that works. I tried using CB_Exit to allow for input but it doesn't copy the input back to the named range on the sheet. Also, I don't seem to getting the new entry into the value property after it's typed in. -------------------------------------------------- ' CBName is the combobox and Cardholder is the named range Private Sub CBName_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim CHName As String CHName = Sheets("Carddata").Range("Cardholder").Cells(1, 1).Value If CHName = "" Then CHName = Me.CBName.Value End If End Sub ---------------------------------------------------- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting input from combobox to update sheet
Hi John, With the following code you need to initially name one cell only as Cardholder. The code assumes this cell to be Cell A1 on worksheet Carddata. As the names are added in the combo box, if not already existing, they are added to the top of the list and the named range for the combo is redefined with the expanded range. The RowSource property for the combo needs to be set to Cardholder. (Can't do this until after cell is named Cardholder.) The MatchRequired property for the combo needs to be false. Do a Find and Replace on the following code to change the sub name from ComboBox1 to match the name of your combo box. Private Sub ComboBox1_AfterUpdate() Dim comboEntry As Variant Dim rngTofind As Range Dim lngRows As Long 'Save the Combo box entry to a variable comboEntry = ComboBox1.Value 'Test if combo box entry exists in Rowsource With Sheets("Carddata") Set rngTofind = .Range("Cardholder") _ .Find(What:=comboEntry, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) End With 'If entry not found then add it to named range If rngTofind Is Nothing Then lngRows = ComboBox1.ListCount + 1 With Sheets("Carddata") 'Move existing Cardholder range down 1 cell 'to allow new entry to be at top of range. .Range("Cardholder").Cut _ Destination:=.Range("A2") 'Redefine Cardholder range to include 'additional cell for new combo box entry. .Range(.Cells(1, 1), _ .Cells(lngRows, 1)) _ .Name = "Cardholder" 'Populate the first cell in the named range 'with the new combo box entry. .Cells(1, 1) = comboEntry End With 'Update the Rowsource for the combo box ComboBox1.RowSource = ("Carddata!Cardholder") End If End Sub -- Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting input from combobox to update sheet
Thanks Jacob and James for the suggestions and advice...I'll let you when I get it working. "John G." wrote: In a userform I'm have a Combobox (CB) that holds names. When the routine is first run list for the CB is empty. The user then inputs a name and this is to be stored for subsequent uses of the workbook. Any other time the form is opened, the ListIndex = 0 will show the user's name for confirmation. The following is another option that I'm contemplating but I need to get the single entry done first: "If another user needs to, they can input their name and it is then added to the top of the list, so that the next time the form is opened this new entry will be the default name shown on the form and the previous entries would show in the dropdown as secondary names." It is my understanding that manual input into a combobox can be stored back to a sheet, preferably the list for the combobox. Can't seem to find a way to start this that works. I tried using CB_Exit to allow for input but it doesn't copy the input back to the named range on the sheet. Also, I don't seem to getting the new entry into the value property after it's typed in. -------------------------------------------------- ' CBName is the combobox and Cardholder is the named range Private Sub CBName_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim CHName As String CHName = Sheets("Carddata").Range("Cardholder").Cells(1, 1).Value If CHName = "" Then CHName = Me.CBName.Value End If End Sub ---------------------------------------------------- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting input from combobox to update sheet
OssieMac...This worked with just one small change. Kept getting a "NULL"
error when I got to CBName.Value. Changed it to CBName.Text and it worked great including the inserting of new names. Thanks "OssieMac" wrote: Hi John, With the following code you need to initially name one cell only as Cardholder. The code assumes this cell to be Cell A1 on worksheet Carddata. As the names are added in the combo box, if not already existing, they are added to the top of the list and the named range for the combo is redefined with the expanded range. The RowSource property for the combo needs to be set to Cardholder. (Can't do this until after cell is named Cardholder.) The MatchRequired property for the combo needs to be false. Do a Find and Replace on the following code to change the sub name from ComboBox1 to match the name of your combo box. Private Sub ComboBox1_AfterUpdate() Dim comboEntry As Variant Dim rngTofind As Range Dim lngRows As Long 'Save the Combo box entry to a variable comboEntry = ComboBox1.Value 'Test if combo box entry exists in Rowsource With Sheets("Carddata") Set rngTofind = .Range("Cardholder") _ .Find(What:=comboEntry, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) End With 'If entry not found then add it to named range If rngTofind Is Nothing Then lngRows = ComboBox1.ListCount + 1 With Sheets("Carddata") 'Move existing Cardholder range down 1 cell 'to allow new entry to be at top of range. .Range("Cardholder").Cut _ Destination:=.Range("A2") 'Redefine Cardholder range to include 'additional cell for new combo box entry. .Range(.Cells(1, 1), _ .Cells(lngRows, 1)) _ .Name = "Cardholder" 'Populate the first cell in the named range 'with the new combo box entry. .Cells(1, 1) = comboEntry End With 'Update the Rowsource for the combo box ComboBox1.RowSource = ("Carddata!Cardholder") End If End Sub -- Regards, OssieMac |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting input from combobox to update sheet
Hi again John, I am assuming that it is this line giving you the problem. comboEntry = ComboBox1.Value I tested the code in xl2007 and xl2002 and no problems. However, the code also works with comboEntry = ComboBox1.Text. Having said that, I have found another problem. If you define the name for the first cell and leave the first cell empty then the empty cell gets pushed down to allow the next new entry and it is continually pushed down and this gives you a blank line at the bottom of the RowSource. The following modified code tests for a blank first cell and if blank it does not push the list down. (I have changed Value to Text). Don't forget to use Find/Replace for ComboBox1. Private Sub ComboBox1_AfterUpdate() Dim comboEntry As Variant Dim rngTofind As Range Dim lngRows As Long 'Save the Combo box entry to a variable comboEntry = ComboBox1.Text 'Test if combo box entry exists in Rowsource With Sheets("Carddata") Set rngTofind = .Range("Cardholder") _ .Find(What:=comboEntry, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) End With 'If entry not found then add it to named range If rngTofind Is Nothing Then lngRows = ComboBox1.ListCount + 1 With Sheets("Carddata") 'Move existing Cardholder range down 1 cell 'to allow new entry to be at top of range. '(Move only if 1st cell in not blank.) If .Range("Cardholder").Cells(1, 1) < "" Then .Range("Cardholder").Cut _ Destination:=.Range("A2") 'Redefine Cardholder range to include 'additional cell for new combo box entry. .Range(.Cells(1, 1), _ .Cells(lngRows, 1)) _ .Name = "Cardholder" End If 'Populate the first cell in the named range 'with the new combo box entry. .Cells(1, 1) = comboEntry End With 'Update the Rowsource for the combo box ComboBox1.RowSource = ("Carddata!Cardholder") End If End Sub -- Regards, OssieMac |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting input from combobox to update sheet
Thx OssieMac...I noticed that when I was ran it. Really appreciate the update. JG "OssieMac" wrote: Hi again John, I am assuming that it is this line giving you the problem. comboEntry = ComboBox1.Value I tested the code in xl2007 and xl2002 and no problems. However, the code also works with comboEntry = ComboBox1.Text. Having said that, I have found another problem. If you define the name for the first cell and leave the first cell empty then the empty cell gets pushed down to allow the next new entry and it is continually pushed down and this gives you a blank line at the bottom of the RowSource. The following modified code tests for a blank first cell and if blank it does not push the list down. (I have changed Value to Text). Don't forget to use Find/Replace for ComboBox1. Private Sub ComboBox1_AfterUpdate() Dim comboEntry As Variant Dim rngTofind As Range Dim lngRows As Long 'Save the Combo box entry to a variable comboEntry = ComboBox1.Text 'Test if combo box entry exists in Rowsource With Sheets("Carddata") Set rngTofind = .Range("Cardholder") _ .Find(What:=comboEntry, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) End With 'If entry not found then add it to named range If rngTofind Is Nothing Then lngRows = ComboBox1.ListCount + 1 With Sheets("Carddata") 'Move existing Cardholder range down 1 cell 'to allow new entry to be at top of range. '(Move only if 1st cell in not blank.) If .Range("Cardholder").Cells(1, 1) < "" Then .Range("Cardholder").Cut _ Destination:=.Range("A2") 'Redefine Cardholder range to include 'additional cell for new combo box entry. .Range(.Cells(1, 1), _ .Cells(lngRows, 1)) _ .Name = "Cardholder" End If 'Populate the first cell in the named range 'with the new combo box entry. .Cells(1, 1) = comboEntry End With 'Update the Rowsource for the combo box ComboBox1.RowSource = ("Carddata!Cardholder") End If End Sub -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Input from ComboBox | Excel Programming | |||
Need to update a ComboBox when selecting a sheet | Excel Worksheet Functions | |||
Combobox options based on the input of another combobox | Excel Programming | |||
ComboBox input | Excel Programming | |||
Compare input from ComboBox | Excel Programming |