Determining the cell position of an object

[Guest]

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]

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.

 

[Guest]

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]

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.

 

[Guest]

Great! thank you and Happy thanksgiving!

Regards,

Chuck

 

[Dave Peterson]

You're welcome.

And Happy Thanksgiving to you and your early frost celebrators!

Great! thank you and Happy thanksgiving!

Regards,

Chuck