Excel Excel vba: "How to make a selection box and assigned it to macro-button"

[fiebuls]

Selection box assigned to macro-button
===============================

I am looking for a way to accomplish the following in Excel (with vba).

- A macro-button called "TIME" on my worksheet.
- When you press the button "TIME" a 'radio-button-selection-box' with 5 predetermined 'hours of the day' should appear.
- The time which will be selected is to be written in the current active cell.

Instead of a 'radio-button-selection-box' another type of selection-box is alright as well.
I do not want a normal listbox in a predetermined cell, because the time that is to be written in the cell could be anywhere (not predefined place) on the sheet.

I know how to assign a macro to a button, but I do not know how to:
* make a 'radio-button-selection-box' appear and call/assign it to a macro.
* read the input once the selection has been made.


I hope my request is stated clearly enough (English is not my native language) and I hope anyone can help me with this problem.

Thanks in advance,
Fie Buls

 

[Stoneboysteve]

Fie Buls,

Create a userform, put a frame on it. Within the frame place the radio buttons.

For the button, use userform.show to expose the form with the buttons on it.

Use the click or change event on the form to capture the value from the selected button to a variable and be sure to include the userform.hide call to close the form.

I think you will need focus on the cell you want to write the value, then use the activecell.value property to write to the cell. Clear the variable and close the routine.

In general, radio buttons are more difficult to work with than a combobox You could use the combobox on the user form with a dropdown of values. The form is not connected to the worksheet and can be called by the macro from any open workbook if you have the macro security set up correctly. The 'Time' button would have to be placed somewhere where you could access it. I would use a custom toolbar instead of a button.

I hope this helps you get started. If you take a stab at it and send along questions I will be glad to help you along. The requirements are a little vague to build your solution from scratch.

Stoneboysteve

 

[fiebuls]

Hi Stoneboysteve,

Along the lines of your advice I managed to get everything working. Thanks!
I still kept to using radiobuttons (I group three of them to be able to select one out of each group) and a macro button instead of the custom toolbar because I am not sure if I can use the workbook "on the road" then.

Bottom line... it all is working, so I am very happy!


Kind regards,
Fie Buls

 

[Stoneboysteve]

Hi Fie Buls,

One trick I use is to keep my 'on the road' macros all on one workbook. That way I can open that workbook in the background and use them in any other workbook.

Stoneboysteve