VBA for Option Button on Userform to send data to Multiple Spreadsheets

**rcfrench** · 03-01-2013, 03:36 PM

Hello all-

I have a rather large userform where I first select a worksheet for data through a list of option buttons and then enter the data on a different page in the same userform. I have been successful in getting data from the data entry fields to populate on the selected worksheet from the first set of radio buttons, but in my data entry section, my radio button (OptionButton1 -- at the bottom of the code) only populates on my master rather on the selected worksheet. It seems to ignore (or not account for) the previous radio button's selection for which worksheet to use for the userform's entry.

Thank for any help in advance! My code:

Please Login or Register  to view this content.

**JBeaucaire** · 03-01-2013, 04:44 PM

Maybe this so you stop running all those IFs once you find the selected RadioButton:

Please Login or Register  to view this content.

Once one of these IFs is true, the ws will be set and all the rest skipped.

**rcfrench** · 03-02-2013, 02:52 PM

Thank you JBeaucaire. I assume this makes my code more efficient, however, the "Yes" from optionbutton1 is still placed on the MASTER sheet rather than on the worksheet selected by the option buttons in the long If/ ElseIf. Is there a way to communicate to the Userform VBA for optionbutton1 that the "Yes" should be only be on the worksheet selected by first set of option buttons in the long If/ ElseIf?

**JBeaucaire** · 03-03-2013, 09:49 PM

You referring to this?:

Please Login or Register  to view this content.

If you want that "Yes" to go to the sheet where the radio button selected a specific sheet, then you'll need to add the same looping "check"

Please Login or Register  to view this content.

**rcfrench** · 03-04-2013, 01:54 PM

Thanks this works great. Data entered on the userform goes to the correct worksheet. I am stumped though on how to get it to enter data in the next available row for optionbutton1. I tried to use the same logic for option buttons for selecting the page however this just returned a Error91.

**JBeaucaire** · 03-04-2013, 04:01 PM

You'll have to explain that, or show your failed coding attempts.

**rcfrench** · 03-08-2013, 11:16 AM

I was able to figure it out. You have been most helpful and again thank you. As a final question, would you have a suggestion on how I can speed up the code with the IF/ ElseIfs?

**JBeaucaire** · 03-08-2013, 11:43 AM

Those aren't particularly slow... and they abort as soon as ONE of them executes a TRUE result.

You might use ScreenUpdating = False near the top of your macro, and ScreenUpdating = True right before you end the Sub.

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.