Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Activate Workbook | Excel Programming | |||
Workbook.Activate / Window.Activate problem | Excel Programming | |||
Workbook.activate | Excel Discussion (Misc queries) | |||
Activate Other Workbook | Excel Programming | |||
Activate Workbook | Excel Programming |