Workbook activate help

[dgold82]

With the help of the community here I was able to find code that helped me
link hundreds of radio buttons on multiple worksheets to cells with a macro.
I am running into a strange situation where sometimes the links are broken
and I have to run the macro again (why is that happening?).

To ease my mind I would like to change the code to the workbook activate
event and have it run each time I open the workbook. I would like to change
the code to run on multiple worksheets (now it just has active worksheet).
Can someone please help me with my code below, I am a beginner with VBA:

Sub LinkOptBtns()
Dim GrpBox As GroupBox
Dim OptBtn As OptionButton
For Each OptBtn In ActiveSheet.OptionButtons
With OptBtn
.LinkedCell = .GroupBox.TopLeftCell.Address
End With
Next
End Sub

Also, if someone knows a way to add a message box that automatically opens
to says something like "workbook loading..." and then close when the macro
completes that would be great!!!

Thanks!

 

[Gary Keramidas]

not sure exactly what you need. but this may help. create a new form, i used
userform1. put a label on it with the text you want displayed "Workbook
Loading.." and format it however you want it to look.

then paste this code on the thisworkbook code page. this is untested and
there is no error checking:
added line numbers so you can tell where it wraps. the line #'s are on
consecutive lines.



Option Explicit

Private Sub Workbook_Open()
Dim GrpBox As GroupBox
Dim OptBtn As OptionButton
Dim ws As Worksheet
10 For Each ws In ThisWorkbook.Worksheets
20 With UserForm1
30 .Show vbModal = False
40 .Label1 = "Working on sheet " & ws.Name 'if you want
updating text for each sheet
50 End With
60 DoEvents ' use this to change the label text during the
opening
70 For Each OptBtn In ws.OptionButtons
80 With OptBtn
90 .LinkedCell = .GroupBox.TopLeftCell.Address
100 End With
110 Next
120 Next
130 Unload UserForm1
End Sub

 

[OssieMac]

I'll stand correcting on this but I thought that controls lost any properties
set with code as soon as the code had finished running and the only way to
have permanent properties is to set them manually with the controls dialog
box.

 

[Gary Keramidas]

are you speaking about the label? if so, i was just showing 2 different
ways. set the label on the form, or add the line of code if the op wants to
have the label update which sheet it's on while it's running. the label
won't be permanently updated with the code.

sorry for the confusion.

 

[OssieMac]

Hi Gary,

"are you speaking about the label?"
No. I was referring to setting the Linked cell property of the Option
button. I thought that the control lost any property set by code as soon as
the code finished running and the only way to set it permanently is via the
properties dialog box. Perhaps I am wrong so I'll have another look at it
tomorrow.

 

[Gary Keramidas]

i don't know about that, i just used the op's code. i did mention i had not
tested it, so i was assuming their code worked.

 

[dgold82]

Thank you so much Gary! It did exactly what I wanted EXCEPT that the workbook
now takes about 5 minutes to load. Probably because there are over 200 group
boxes with 4 radio buttons on 4 different worksheets.

I think I should be focusing on why original code is not permanently linking
a group of radio buttons to the cell below them. (My worksheet with all these
radio buttons mimics a scantron students take with little radio buttons for
"A" "B" "C" "D" like a standardized test)

OssieMac alluded to this problem in one of the replies. Any idea how to make
the link permanent. This would be the best solution.