Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default Workbook activate help

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!
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,494
Default Workbook activate help

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


--

Gary Keramidas
Excel 2003


"dgold82" wrote in message
...
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!


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,510
Default Workbook activate help

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.

--
Regards,

OssieMac


"Gary Keramidas" wrote:

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


--

Gary Keramidas
Excel 2003


"dgold82" wrote in message
...
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!



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,494
Default Workbook activate help

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.

--

Gary Keramidas
Excel 2003


"OssieMac" wrote in message
...
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.

--
Regards,

OssieMac


"Gary Keramidas" wrote:

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


--

Gary Keramidas
Excel 2003


"dgold82" wrote in message
...
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!




  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,510
Default Workbook activate help

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.

--
Regards,

OssieMac


"Gary Keramidas" wrote:

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.

--

Gary Keramidas
Excel 2003


"OssieMac" wrote in message
...
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.

--
Regards,

OssieMac


"Gary Keramidas" wrote:

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


--

Gary Keramidas
Excel 2003


"dgold82" wrote in message
...
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!






  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,494
Default Workbook activate help

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.

--

Gary Keramidas
Excel 2003


"OssieMac" wrote in message
...
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.

--
Regards,

OssieMac


"Gary Keramidas" wrote:

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.

--

Gary Keramidas
Excel 2003


"OssieMac" wrote in message
...
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.

--
Regards,

OssieMac


"Gary Keramidas" wrote:

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


--

Gary Keramidas
Excel 2003


"dgold82" wrote in message
...
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!





  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default Workbook activate help

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.

"Gary Keramidas" wrote:

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


--

Gary Keramidas
Excel 2003


"dgold82" wrote in message
...
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!



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Activate Workbook hshayh0rn Excel Programming 4 February 13th 06 11:09 PM
Workbook.Activate / Window.Activate problem Tim[_44_] Excel Programming 3 February 4th 06 12:38 AM
Workbook.activate Jeff Excel Discussion (Misc queries) 1 December 13th 04 11:22 PM
Activate Other Workbook pauluk[_37_] Excel Programming 16 April 20th 04 07:02 PM
Activate Workbook Fred[_16_] Excel Programming 1 December 2nd 03 06:15 PM


All times are GMT +1. The time now is 02:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"