Multiple ComboBoxes and TextBoxes on Userform

[Guest]

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.

 

[Harald Staff]

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.

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.

 

[Guest]

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

 

[Harald Staff]

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.

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.

 

[Doug Glancy]

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

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.

 

[Guest]

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

 

[Guest]

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.