Link to home
Create AccountLog in
Avatar of kerrybenno
kerrybenno

asked on

Spin Button and Textboxes Excel VBA

I have a userform in Excel with 10 textboxes that require dates, the date in each textbox is currently controlled by its own spin button.  Is there a way to have one piece of code that when the spin button is pressed, it knows what textbox this relates to.
I thought by grouping together the spin button and relevant textbox that I may be able to do it, but i've not figured a way yet.
Avatar of CheetoBandito
CheetoBandito

Each spin button will have it's own _Change() event.  If you edit that event and insert code to reference the appropriate text box, they will effectively be 'linked'.

Alternatively you use the Me.Name property to get the name of the spin button.  If you name all your textboxs the same base name with a different number at the end, and then do the same for the spinbuttons.  You can do a Right(Me.Name, 1) to get the number of the spin button and then right a function passing that number as an argument, and then update the corresponding textbox based on the number.

Depending on how big of a form this is, it might not be worth it to write this function...you might be better off just copying-pasting the block and changing the appropriate values.
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account