Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple ComboBoxes and TextBoxes on Userform
I have designed a Userform to make it easier to enter a lot of data on a spreadsheet. There are over a hundred textboxes and 44 comboboxes. Is there a way to detect when any combobox or textbox changes and retrieve the name of only the one that is changed ? I have 44 subroutines - Sub combobox1_change() to sub combobox44_change(). I've tried Userform_change to try to retrieve the name but that doesn't detect a change in a combobox. If I could get that information I could use the number from the name to calculate what row on the spreadsheet to get or put data and do it with one routine instead of 44 for the comboboxes. I don't relish writing over a 100 subroutines for the textboxes
Thanks for any help here and all the pointers I've gotten from other peoples' questions and the solutions offered. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple ComboBoxes and TextBoxes on Userform
Hi
Check their _Click events as well. .... ok, another 44 routines to do. The alternative is to assign them to combobox classes and have that single class report the changes back. But then there's 44 class assignments to write first. -- HTH. Best wishes Harald Followup to newsgroup only please. "Rich J" wrote in message ... I have designed a Userform to make it easier to enter a lot of data on a spreadsheet. There are over a hundred textboxes and 44 comboboxes. Is there a way to detect when any combobox or textbox changes and retrieve the name of only the one that is changed ? I have 44 subroutines - Sub combobox1_change() to sub combobox44_change(). I've tried Userform_change to try to retrieve the name but that doesn't detect a change in a combobox. If I could get that information I could use the number from the name to calculate what row on the spreadsheet to get or put data and do it with one routine instead of 44 for the comboboxes. I don't relish writing over a 100 subroutines for the textboxes. Thanks for any help here and all the pointers I've gotten from other peoples' questions and the solutions offered. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple ComboBoxes and TextBoxes on Userform
Thanks for commenting Harald. Luckily I don't think I need the _click event. What I need seems to work on _change.
I found this code in a help from a while back on here but I don't understand all how it works and it is dependent on clicking a specifice combobox. I followed the actions and it makes any combobox that does not have a ' 1 ' invisible. There is a clue to what I need but can't put it into practice. The names are detected but still need a way to detect any combo or text box that is changed. Private Sub combobox1_Click() For Each CTRL In UserForm3.Controls If TypeOf CTRL Is MsForms.ComboBox Then Set CBOX = CTRL If Right(CBOX.Name, 1) < 1 Then CBOX.Visible = False End If End If Next Controls("Combobox" & ComboBox1.ListIndex + 2).Visible = True Me.Repaint End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple ComboBoxes and TextBoxes on Userform
Sorry, you lost me my friend. "Change" will trap change events, and you state that it does
that well. So just put something in each Change event that screams out which one that changed. ? I am probably missing something. -- HTH. Best wishes Harald Followup to newsgroup only please. "Rich J" wrote in message ... Thanks for commenting Harald. Luckily I don't think I need the _click event. What I need seems to work on _change. I found this code in a help from a while back on here but I don't understand all how it works and it is dependent on clicking a specifice combobox. I followed the actions and it makes any combobox that does not have a ' 1 ' invisible. There is a clue to what I need but can't put it into practice. The names are detected but still need a way to detect any combo or text box that is changed. Private Sub combobox1_Click() For Each CTRL In UserForm3.Controls If TypeOf CTRL Is MsForms.ComboBox Then Set CBOX = CTRL If Right(CBOX.Name, 1) < 1 Then CBOX.Visible = False End If End If Next Controls("Combobox" & ComboBox1.ListIndex + 2).Visible = True Me.Repaint End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple ComboBoxes and TextBoxes on Userform
Rich,
It seems to me you want a class event, as Harald said in his first response. Take a look at this link on John Walkenbach's site and see if it helps: http://j-walk.com/ss/excel/tips/tip44.htm hth, Doug "Rich J" wrote in message ... I have designed a Userform to make it easier to enter a lot of data on a spreadsheet. There are over a hundred textboxes and 44 comboboxes. Is there a way to detect when any combobox or textbox changes and retrieve the name of only the one that is changed ? I have 44 subroutines - Sub combobox1_change() to sub combobox44_change(). I've tried Userform_change to try to retrieve the name but that doesn't detect a change in a combobox. If I could get that information I could use the number from the name to calculate what row on the spreadsheet to get or put data and do it with one routine instead of 44 for the comboboxes. I don't relish writing over a 100 subroutines for the textboxes. Thanks for any help here and all the pointers I've gotten from other peoples' questions and the solutions offered. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple ComboBoxes and TextBoxes on Userform
I am doing that in each
private Sub ComboBox#_change() R= 5 <-corresponds to the row on the spreadsheet subroutine <- goes to a routine that modifies various cells in that row end sub I wrote that 22 times for the comboboxes and 44 times for the textboxes that need to have data added I wanted to know if there is code to detect when any combobox or textbox is changed no matter which one it is. Instead of having 66 subroutines, one for each, I want to be able to detect CBOX.name and TBOX.name and extract the number from the name and use it to calculate R. ex. PRIVATE SUB ANYCTRL_CHANGE() If Type of CTRL is MSForms.ComboBox Then CBOX.name = combobox502 TEMP = right(CBOX.name,3) <- would extract 502 as text from name of combobox R = (TEMP * 1) - 497 <- *1 would convert to integer and then R would be 5 ELSEIF Type of CTRL is MSForms.TextBox Then |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple ComboBoxes and TextBoxes on Userform
Thanks Doug, I'm going thru the coding now to figure out how to use it but that is exactly what I'm looking for.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UserForm TextBoxes | Excel Discussion (Misc queries) | |||
Clear Comboboxes & Textboxes | New Users to Excel | |||
userform textboxes again | Excel Programming | |||
userform textboxes | Excel Programming | |||
Several comboBoxes on a Userform | Excel Programming |