Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining the cell position of an object
Help would be very appreciated, I am actually working on a worksheet where I
have a certain number of Toggle buttons on each row but on a unique column. On column B, I have a numbering system reflecting a WBS (Work breakdown structure number) and each time I click on a particular Toggle button of a particular row number I would like the event to do the following: Unhide let us say the next 3 rows and within these rows I would have a document name, which I could click over and get the the document written in word to open for Editing or viewing. Now once the closing the word document, I may click the same Toggle button to Hide those same unhidden rows. Can anyone help me achieve such thing? I am new to VBA and unable to program such event. Thank you in advance for your comments and help! Regards, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining the cell position of an object
If you use a togglebutton from the Control toolbox toolbar, you're going to need
a procedure for each of those buttons. I'd use a button from the Forms toolbar and toggle the caption: Then I could add as many buttons as I want and assign them to the same macro: Option Explicit Sub ClickButton() Dim BTN As Button Set BTN = ActiveSheet.Buttons(Application.Caller) If LCase(BTN.Caption) = LCase("Hide Rows") Then BTN.TopLeftCell.Offset(1, 0).Resize(3, 1).EntireRow.Hidden = True BTN.Caption = "Show Rows" Else BTN.TopLeftCell.Offset(1, 0).Resize(3, 1).EntireRow.Hidden = False BTN.Caption = "Hide Rows" End If End Sub And you should be able to use a worksheet function: =hyperlink("File:////yourpathtoyourword.doc","Click me!") to start up your MSWord document. Chuck H. wrote: Help would be very appreciated, I am actually working on a worksheet where I have a certain number of Toggle buttons on each row but on a unique column. On column B, I have a numbering system reflecting a WBS (Work breakdown structure number) and each time I click on a particular Toggle button of a particular row number I would like the event to do the following: Unhide let us say the next 3 rows and within these rows I would have a document name, which I could click over and get the the document written in word to open for Editing or viewing. Now once the closing the word document, I may click the same Toggle button to Hide those same unhidden rows. Can anyone help me achieve such thing? I am new to VBA and unable to program such event. Thank you in advance for your comments and help! Regards, -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining the cell position of an object
Thanks Dave for your prompt and very efficient resolution! it works
perfectly. Just for curiosity, is there a way to change the button color the way we can do for the Toggle or regular command button? and why is it that the reaction using a command button or a toggle button one has to create as manay procedures as they are buttons? Thanks in advance! Chuck "Dave Peterson" wrote: If you use a togglebutton from the Control toolbox toolbar, you're going to need a procedure for each of those buttons. I'd use a button from the Forms toolbar and toggle the caption: Then I could add as many buttons as I want and assign them to the same macro: Option Explicit Sub ClickButton() Dim BTN As Button Set BTN = ActiveSheet.Buttons(Application.Caller) If LCase(BTN.Caption) = LCase("Hide Rows") Then BTN.TopLeftCell.Offset(1, 0).Resize(3, 1).EntireRow.Hidden = True BTN.Caption = "Show Rows" Else BTN.TopLeftCell.Offset(1, 0).Resize(3, 1).EntireRow.Hidden = False BTN.Caption = "Hide Rows" End If End Sub And you should be able to use a worksheet function: =hyperlink("File:////yourpathtoyourword.doc","Click me!") to start up your MSWord document. Chuck H. wrote: Help would be very appreciated, I am actually working on a worksheet where I have a certain number of Toggle buttons on each row but on a unique column. On column B, I have a numbering system reflecting a WBS (Work breakdown structure number) and each time I click on a particular Toggle button of a particular row number I would like the event to do the following: Unhide let us say the next 3 rows and within these rows I would have a document name, which I could click over and get the the document written in word to open for Editing or viewing. Now once the closing the word document, I may click the same Toggle button to Hide those same unhidden rows. Can anyone help me achieve such thing? I am new to VBA and unable to program such event. Thank you in advance for your comments and help! Regards, -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining the cell position of an object
It's the difference between the controls on the Forms toolbar and the controls
on the Control toolbox toolbar. With the Forms toolbar controls, you assign a macro. With the control toolbox toolbar controls, you can double click on that control and see the event that runs when you click it. You can change the text on that button: Option Explicit Sub ClickButton() Dim BTN As Button Set BTN = ActiveSheet.Buttons(Application.Caller) If LCase(BTN.Caption) = LCase("Hide Rows") Then BTN.TopLeftCell.Offset(1, 0).Resize(3, 1).EntireRow.Hidden = True BTN.Caption = "Show Rows" BTN.Font.ColorIndex = 3 Else BTN.TopLeftCell.Offset(1, 0).Resize(3, 1).EntireRow.Hidden = False BTN.Caption = "Hide Rows" BTN.Font.ColorIndex = 5 End If End Sub (Use colors that you can see!) But you can use other shapes (from the drawing toolbar???) that you can make prettier, too. Chuck H. wrote: Thanks Dave for your prompt and very efficient resolution! it works perfectly. Just for curiosity, is there a way to change the button color the way we can do for the Toggle or regular command button? and why is it that the reaction using a command button or a toggle button one has to create as manay procedures as they are buttons? Thanks in advance! Chuck "Dave Peterson" wrote: If you use a togglebutton from the Control toolbox toolbar, you're going to need a procedure for each of those buttons. I'd use a button from the Forms toolbar and toggle the caption: Then I could add as many buttons as I want and assign them to the same macro: Option Explicit Sub ClickButton() Dim BTN As Button Set BTN = ActiveSheet.Buttons(Application.Caller) If LCase(BTN.Caption) = LCase("Hide Rows") Then BTN.TopLeftCell.Offset(1, 0).Resize(3, 1).EntireRow.Hidden = True BTN.Caption = "Show Rows" Else BTN.TopLeftCell.Offset(1, 0).Resize(3, 1).EntireRow.Hidden = False BTN.Caption = "Hide Rows" End If End Sub And you should be able to use a worksheet function: =hyperlink("File:////yourpathtoyourword.doc","Click me!") to start up your MSWord document. Chuck H. wrote: Help would be very appreciated, I am actually working on a worksheet where I have a certain number of Toggle buttons on each row but on a unique column. On column B, I have a numbering system reflecting a WBS (Work breakdown structure number) and each time I click on a particular Toggle button of a particular row number I would like the event to do the following: Unhide let us say the next 3 rows and within these rows I would have a document name, which I could click over and get the the document written in word to open for Editing or viewing. Now once the closing the word document, I may click the same Toggle button to Hide those same unhidden rows. Can anyone help me achieve such thing? I am new to VBA and unable to program such event. Thank you in advance for your comments and help! Regards, -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining the cell position of an object
Great! thank you and Happy thanksgiving!
Regards, Chuck "Dave Peterson" wrote: It's the difference between the controls on the Forms toolbar and the controls on the Control toolbox toolbar. With the Forms toolbar controls, you assign a macro. With the control toolbox toolbar controls, you can double click on that control and see the event that runs when you click it. You can change the text on that button: Option Explicit Sub ClickButton() Dim BTN As Button Set BTN = ActiveSheet.Buttons(Application.Caller) If LCase(BTN.Caption) = LCase("Hide Rows") Then BTN.TopLeftCell.Offset(1, 0).Resize(3, 1).EntireRow.Hidden = True BTN.Caption = "Show Rows" BTN.Font.ColorIndex = 3 Else BTN.TopLeftCell.Offset(1, 0).Resize(3, 1).EntireRow.Hidden = False BTN.Caption = "Hide Rows" BTN.Font.ColorIndex = 5 End If End Sub (Use colors that you can see!) But you can use other shapes (from the drawing toolbar???) that you can make prettier, too. Chuck H. wrote: Thanks Dave for your prompt and very efficient resolution! it works perfectly. Just for curiosity, is there a way to change the button color the way we can do for the Toggle or regular command button? and why is it that the reaction using a command button or a toggle button one has to create as manay procedures as they are buttons? Thanks in advance! Chuck "Dave Peterson" wrote: If you use a togglebutton from the Control toolbox toolbar, you're going to need a procedure for each of those buttons. I'd use a button from the Forms toolbar and toggle the caption: Then I could add as many buttons as I want and assign them to the same macro: Option Explicit Sub ClickButton() Dim BTN As Button Set BTN = ActiveSheet.Buttons(Application.Caller) If LCase(BTN.Caption) = LCase("Hide Rows") Then BTN.TopLeftCell.Offset(1, 0).Resize(3, 1).EntireRow.Hidden = True BTN.Caption = "Show Rows" Else BTN.TopLeftCell.Offset(1, 0).Resize(3, 1).EntireRow.Hidden = False BTN.Caption = "Hide Rows" End If End Sub And you should be able to use a worksheet function: =hyperlink("File:////yourpathtoyourword.doc","Click me!") to start up your MSWord document. Chuck H. wrote: Help would be very appreciated, I am actually working on a worksheet where I have a certain number of Toggle buttons on each row but on a unique column. On column B, I have a numbering system reflecting a WBS (Work breakdown structure number) and each time I click on a particular Toggle button of a particular row number I would like the event to do the following: Unhide let us say the next 3 rows and within these rows I would have a document name, which I could click over and get the the document written in word to open for Editing or viewing. Now once the closing the word document, I may click the same Toggle button to Hide those same unhidden rows. Can anyone help me achieve such thing? I am new to VBA and unable to program such event. Thank you in advance for your comments and help! Regards, -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining the cell position of an object
You're welcome.
And Happy Thanksgiving to you and your early frost celebrators! Chuck H. wrote: Great! thank you and Happy thanksgiving! Regards, Chuck "Dave Peterson" wrote: It's the difference between the controls on the Forms toolbar and the controls on the Control toolbox toolbar. With the Forms toolbar controls, you assign a macro. With the control toolbox toolbar controls, you can double click on that control and see the event that runs when you click it. You can change the text on that button: Option Explicit Sub ClickButton() Dim BTN As Button Set BTN = ActiveSheet.Buttons(Application.Caller) If LCase(BTN.Caption) = LCase("Hide Rows") Then BTN.TopLeftCell.Offset(1, 0).Resize(3, 1).EntireRow.Hidden = True BTN.Caption = "Show Rows" BTN.Font.ColorIndex = 3 Else BTN.TopLeftCell.Offset(1, 0).Resize(3, 1).EntireRow.Hidden = False BTN.Caption = "Hide Rows" BTN.Font.ColorIndex = 5 End If End Sub (Use colors that you can see!) But you can use other shapes (from the drawing toolbar???) that you can make prettier, too. Chuck H. wrote: Thanks Dave for your prompt and very efficient resolution! it works perfectly. Just for curiosity, is there a way to change the button color the way we can do for the Toggle or regular command button? and why is it that the reaction using a command button or a toggle button one has to create as manay procedures as they are buttons? Thanks in advance! Chuck "Dave Peterson" wrote: If you use a togglebutton from the Control toolbox toolbar, you're going to need a procedure for each of those buttons. I'd use a button from the Forms toolbar and toggle the caption: Then I could add as many buttons as I want and assign them to the same macro: Option Explicit Sub ClickButton() Dim BTN As Button Set BTN = ActiveSheet.Buttons(Application.Caller) If LCase(BTN.Caption) = LCase("Hide Rows") Then BTN.TopLeftCell.Offset(1, 0).Resize(3, 1).EntireRow.Hidden = True BTN.Caption = "Show Rows" Else BTN.TopLeftCell.Offset(1, 0).Resize(3, 1).EntireRow.Hidden = False BTN.Caption = "Hide Rows" End If End Sub And you should be able to use a worksheet function: =hyperlink("File:////yourpathtoyourword.doc","Click me!") to start up your MSWord document. Chuck H. wrote: Help would be very appreciated, I am actually working on a worksheet where I have a certain number of Toggle buttons on each row but on a unique column. On column B, I have a numbering system reflecting a WBS (Work breakdown structure number) and each time I click on a particular Toggle button of a particular row number I would like the event to do the following: Unhide let us say the next 3 rows and within these rows I would have a document name, which I could click over and get the the document written in word to open for Editing or viewing. Now once the closing the word document, I may click the same Toggle button to Hide those same unhidden rows. Can anyone help me achieve such thing? I am new to VBA and unable to program such event. Thank you in advance for your comments and help! Regards, -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I fix the position of an object on the screen? | Excel Discussion (Misc queries) | |||
Position drawing object relative to cell | Excel Discussion (Misc queries) | |||
Object Position | Excel Programming | |||
Line Position Drawing Object | Excel Programming | |||
Determining chart points at mouse position | Excel Programming |