Control Freak!! User Forms

Posted by wiggy on May 24, 2001 3:09 AM

Control freak. User Forms

I have designed a nice little user form, with a combination of option buttons, check boxes and command buttons.

Having assigned macros to each of the buttons, I now realise my mistake. As soon as I select any of the option buttons or check boxes the code associated with them runs (not a bad thing I hear you say!). But being the control freak that I am, I would like to be able to audit my selections before the code runs, i.e. to have the code run for each of the selected options once another button is pressed.

The macros for each of the buttons just select a particular range of information from a worksheet and paste it into another.
Does anyone have any bright ideas for giving me the power I crave!! :-p

Thanks for all suggestions

Posted by Dave Hawley on May 24, 2001 3:24 AM

Hi Wiggy

You say "UserForm" but also say you have assingned Macros to them which would suggest they are on a Worksheet. So I need to ask are the Controls on a UserForm ? If they are not and are on a Worksheet are they from the "Control toolbox" or "Forms" ?

Dave

If you only want the user to have one choice at the time, you should use OptionButtons. OzGrid Business Applications

Posted by wiggy on May 24, 2001 4:25 AM

If you only want the user to have one choice at the time, you should use OptionButtons.
Dave,

Thanks for getting back to me,

It is a UserForm I am trying to use, I have a frame containing 5 options for the user to select, these are the conditions that will always apply, i.e. location of incident.
The rest of the form has a number of checkboxes for conditions that may or may not exist.

The code I have assigned to each button takes the form of; Application.Run "'Text details.xls'!fetch_X1"

Where fetch_Road_X1 is a macro, which extracts a named range of cells from a worksheet and pastes it into specified cells in another.

As soon as a checkbox is selected the code runs and updates the relevant worksheets,

What I hope to be able to achieve; is to put the selections from one of the options buttons and any checkboxes that may be applicable into a buffer before they are executed so that the user can check the information before committing the data to the worksheet.
As it stands if the user selects the wrong checkbox, and realises his mistake early on in the form completion process, they will have to carry on to the end and rectify the error manually on as many as six worksheets., (kind of obviating the use of a userform)

I realise I need to remove the application.run condition directly from the buttons but cant figure out how to produce the intermediate step that still uses the selected option and checkboxes

Thanks once again for your suggestions

Wiggy

Posted by David Hawley on May 24, 2001 4:43 AM

Ok, What you need to do is declare a Procedure level String Variable. That is put something like:

Dim sChoice as String

at the VERY top of the UserForms Private Module. Then in each OptionButton put some code that will parse the Macro name to the Variable. Then use a commandbutton to run your code:

Dim sChoice As String

Private Sub OptionButton1_Click()
sChoice = "Macro1"
End Sub
Private Sub OptionButton2_Click()
sChoice = "Macro2"
End Sub

Private Sub CommandButton1_Click()
Run sChoice
End Sub

Dave

OzGrid Business Applications