VBA that applies for multiple option buttons

[D_C_X]

Hi everyone!

Please can someone help me out with the following:

There are 29 option buttons in a group box, and each option button activates a macro when selected. The option buttons are placed in collum V. The user can put data in the cell next tot it in collum W.

The code will put a timestamp in cell U55 and the value of the cell next to the option button, collum W, in cell V55.

Sub OptionButton145_Klikken()
response = MsgBox("Key given to " & Range("W23").Value & "", vbOKOnly)
Application.Range("U55") = Now()
Application.Range("V55") = "Key given to " & Range("W23").Value & ""
Activeworkbook.Save
End Sub

Now I am assigning a new macro to every individual option button (232 in total...), and paste the above code and change it into the next cell vallue. Is there any way to make this macro apply for this group of option buttons?

Also i'm wondering if it's possible to change the vbOKOnly into vbYesNo, with when vbNo is selected the last checked option button is selected, in stead of the new one clicked. And proceed the above code when vbYes.

Thanks in advance,
D_C_X

 

[My Aswer Is This]

DCX:
I do not believe you should need 232 option buttons on one sheet.
Please tell us what your trying to do and maybe we can give you a easier way to do this.
In previous postings from others we have eliminated the need for a thousand check boxes down to one script. Maybe we can do the same for you.

 

[GTO]

There are 29 option buttons in a group box....

Hi there and welcome to the forum

Based on the Sub being Public and the mention of a Group Box, I am guessing, but please confirm, you are using the older Form type controls (rather than ActiveX), is that correct?

Also i'm wondering if it's possible to change the vbOKOnly into vbYesNo, with when vbNo is selected the last checked option button is selected, in stead of the new one clicked. And proceed the above code when vbYes.

Maybe close to:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Sub</SPAN> OptionButton1_Click()<br>  <br>  <SPAN style="color:#00007F">If</SPAN> MsgBox("Change Key given to " & Range("W23").Value & "?", vbYesNo <SPAN style="color:#00007F">Or</SPAN> vbDefaultButton2, vbNullString) = vbYes <SPAN style="color:#00007F">Then</SPAN><br>    <SPAN style="color:#007F00">'Do whatever</SPAN><br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>  <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Does that help?

Mark

 

[D_C_X]

DCX:
I do not believe you should need 232 option buttons on one sheet.
Please tell us what your trying to do and maybe we can give you a easier way to do this.
In previous postings from others we have eliminated the need for a thousand check boxes down to one script. Maybe we can do the same for you.

Thanks! I sure hoped there was an easier way doing this.
I'm making a room reservation ''program'': we have 8 rooms available for meetings. In this sheet you see a table, wich displays a day per row. And per roomnumber (1 - 8) i have 2 collums per room. In the first cell the option button, in the second cell next to it a blank cell where the members using this program can enter the person's name.

My intention is to make this so that it is tracable and logbooked what changes are made and by who is using the program. I've got that quit figured out and up and running, gladly

Please let me know if this is enough.

 

[D_C_X]

Hi there and welcome to the forum

Based on the Sub being Public and the mention of a Group Box, I am guessing, but please confirm, you are using the older Form type controls (rather than ActiveX), is that correct?

Thanks GTO! That is correct. I'm still learning Excel and am using it now for programming for about 4 weeks now. Must say, getting more and more exciting while i'm progressing

Option Explicit

Sub OptionButton1_Click()

If MsgBox("Change Key given to " & Range("W23").Value & "?", vbYesNo Or vbDefaultButton2, vbNullString) = vbYes Then
'Do whatever
End If

End Sub

Does that help?

No that doesn't seem to make any difference then displaying a yes and no button. The code does stop when No is selected, and continues when Yes is chosen, but still the option button stays at the newly selected one, in stead of 'canceling' and returning to the last selected. Perhaps it's possible to extract data from my logbook-sheet, referring to the unique values (due to timestamp) and link that in a code?

Thanks in advance!

 

[My Aswer Is This]

You have one row per date.
The Date is in what column?
Then you have an option button in Column "A" of every row I assume
Show me the script in the option button.
And then your using two columns per room (8) rooms
So I'm assuming you have an option button for every day of the year.

Show me the script you have in your option buttons.
I'm sure we can make this easier for you.

 

[D_C_X]

You have one row per date.
The Date is in what column?

C17 - C45 (29 days, Februari).

Then you have an option button in Column "A" of every row I assume
Show me the script in the option button.

True. Column ''A'' the option button, column ''B'' the blank cell for name input.

Here is the code, translated in English.

Sub Keuzerondje139_Klikken()
response = MsgBox("Key 3 returned", vbOKOnly)
Application.Range("U55") = Now()
Application.Range("V55") = "Key 3 returned"""
End Sub


Sub Keuzerondje138_Klikken()
response = MsgBox("Key 3 given to " & Range("W17").Value & "", vbOKOnly)
Application.Range("U55") = Now()
Application.Range("V55") = "Key 3 given to " & Range("W17").Value & ""
End Sub


Sub Keuzerondje140_Klikken()
response = MsgBox("Key 3 given to " & Range("W18").Value & "", vbOKOnly)
Application.Range("U55") = Now()
Application.Range("V55") = "Key 3 given to " & Range("W18").Value & ""
End Sub


Sub Keuzerondje141_Klikken()
response = MsgBox("Key 3 given to " & Range("W19").Value & "", vbOKOnly)
Application.Range("U55") = Now()
Application.Range("V55") = "Key 3 given to '' & Range("W19").Value & ""
End Sub

And then your using two columns per room (8) rooms
So I'm assuming you have an option button for every day of the year.

Yes. Well in fact 8 option buttons per row for 8 different keys. Plus on top of the column, for each merged cell of two collums, showing the roomnumber, optionbutton 139, wich says the key is back in our hands.

Thanks a lot!