Location of control button

  • Thread starter Thread starter Thomas Tremain
  • Start date Start date
T

Thomas Tremain

I have the need to use a control button to run a script, however, I need the
script
to be able to tell the current position in the worksheet of that button.

The worksheet is sorted regularly, in many different ways, so I nned to be
able to locate
at least the ROW where the button is located, because I will have a simular
button on
each row.

Any help is greatly appreciated!

news at thomas.tremaininc.com
www.LiveHost.net
www.GotoNames.com
www.TrafficExaminer.com
 
Take a look at the .topleftcell of the controlbutton.

msgbox worksheets("sheet1").commandbutton1.topleftcell.row
 
Great! I just tried this, and get:

Sub Button130_Click()
MsgBox Worksheets("clients").CommandButton130.TopLeftCell.row
End Sub

I get "this object does not support this property or method"

I also tried changing the sub to CommandButton130_Click() and tried to
remove the
"command" from the lower line... Every combination of those two changes..

Suggestions?

Thank you!
 
In a private reply:

It looks like the button is called Button130, not commandbutton130.

And is it on the Clients worksheet?
 
In another private reply:

try rightclicking on the button and looking in the name box (to the left of the
formulabar).

But if this is a button from the control toolbox toolbar, then when you
rightclick on the button and select properties, you'll see the name in the
(name) box--right at the top of the list.


--
Dave Peterson
(e-mail address removed)


----- Original Message -----
From: "Thomas Tremain" <[email protected]>
To: "'Dave Peterson'" <[email protected]>
Sent: Monday, November 24, 2003 09:37
Subject: RE: Location of control button



I honestly cannot figure out how to get to the name of the button.

I can't just right click and go to properties... It's driving me nuts.

I have tried both names however..

Thomas Tremain
www.LiveHost.net
www.GotoNames.com
www.TrafficExaminer.com


-----Original Message-----
From: Dave Peterson [mailto:[email protected]]
Sent: Monday, November 24, 2003 6:26 AM
To: Thomas Tremain
Subject: Re: Location of control button


It looks like the button is called Button130, not commandbutton130.

And is it on the Clients worksheet?

--
Dave Peterson
(e-mail address removed)


----- Original Message -----
From: "Thomas Tremain" <[email protected]>
To: <[email protected]>
Sent: Monday, November 24, 2003 03:45
Subject: Re: Location of control button


Great! I just tried this, and get:

Sub Button130_Click()
MsgBox Worksheets("clients").CommandButton130.TopLeftCell.row
End Sub

I get "this object does not support this property or method"

I also tried changing the sub to CommandButton130_Click() and tried to
remove the
"command" from the lower line... Every combination of those two changes..

Suggestions?

Thank you!
 
That would explain why I couldn't find properties... I was creating a
command button from
the Forms Toolbar. It just wasn't working well for me.

Is it possible to create a commandbutton array (So I only have to write one
script instead of 200
identical?)

Thomas Tremain
www.LiveHost.net
www.GotoNames.com
www.TrafficExaminer.com


Dave Peterson said:
In another private reply:

try rightclicking on the button and looking in the name box (to the left of the
formulabar).

But if this is a button from the control toolbox toolbar, then when you
rightclick on the button and select properties, you'll see the name in the
(name) box--right at the top of the list.


--
Dave Peterson
(e-mail address removed)


----- Original Message -----
From: "Thomas Tremain" <[email protected]>
To: "'Dave Peterson'" <[email protected]>
Sent: Monday, November 24, 2003 09:37
Subject: RE: Location of control button



I honestly cannot figure out how to get to the name of the button.

I can't just right click and go to properties... It's driving me nuts.

I have tried both names however..

Thomas Tremain
www.LiveHost.net
www.GotoNames.com
www.TrafficExaminer.com


-----Original Message-----
From: Dave Peterson [mailto:[email protected]]
Sent: Monday, November 24, 2003 6:26 AM
To: Thomas Tremain
Subject: Re: Location of control button


It looks like the button is called Button130, not commandbutton130.

And is it on the Clients worksheet?

--
Dave Peterson
(e-mail address removed)


----- Original Message -----
From: "Thomas Tremain" <[email protected]>
To: <[email protected]>
Sent: Monday, November 24, 2003 03:45
Subject: Re: Location of control button


Great! I just tried this, and get:

Sub Button130_Click()
MsgBox Worksheets("clients").CommandButton130.TopLeftCell.row
End Sub

I get "this object does not support this property or method"

I also tried changing the sub to CommandButton130_Click() and tried to
remove the
"command" from the lower line... Every combination of those two changes..

Suggestions?

Thank you!

Dave Peterson said:
Take a look at the .topleftcell of the controlbutton.

msgbox worksheets("sheet1").commandbutton1.topleftcell.row
Click to expand...
need
the
Click to expand...
to
be
Click to expand...
Click to expand...
 
Keep the button from the Forms toolbar and you'll be ok. Just assign each
button the same macro:

then you can get the topleftcell.row with something like:

msgbox activesheet.buttons(application.caller).topleftcell.row

The application.caller will return the name of the button that was clicked.

I find using the Forms version of the button easier to use when the macro is
going to do identical things.

====
You could also use the buttons from the control toolbox toolbar and have each
_click call a common macro and pass it the row. (but why bother if you already
have the buttons located?)


Thomas said:
That would explain why I couldn't find properties... I was creating a
command button from
the Forms Toolbar. It just wasn't working well for me.

Is it possible to create a commandbutton array (So I only have to write one
script instead of 200
identical?)

Thomas Tremain
www.LiveHost.net
www.GotoNames.com
www.TrafficExaminer.com

Dave Peterson said:
In another private reply:

try rightclicking on the button and looking in the name box (to the left of the
formulabar).

But if this is a button from the control toolbox toolbar, then when you
rightclick on the button and select properties, you'll see the name in the
(name) box--right at the top of the list.


--
Dave Peterson
(e-mail address removed)


----- Original Message -----
From: "Thomas Tremain" <[email protected]>
To: "'Dave Peterson'" <[email protected]>
Sent: Monday, November 24, 2003 09:37
Subject: RE: Location of control button



I honestly cannot figure out how to get to the name of the button.

I can't just right click and go to properties... It's driving me nuts.

I have tried both names however..

Thomas Tremain
www.LiveHost.net
www.GotoNames.com
www.TrafficExaminer.com


-----Original Message-----
From: Dave Peterson [mailto:[email protected]]
Sent: Monday, November 24, 2003 6:26 AM
To: Thomas Tremain
Subject: Re: Location of control button


It looks like the button is called Button130, not commandbutton130.

And is it on the Clients worksheet?

--
Dave Peterson
(e-mail address removed)


----- Original Message -----
From: "Thomas Tremain" <[email protected]>
To: <[email protected]>
Sent: Monday, November 24, 2003 03:45
Subject: Re: Location of control button


Great! I just tried this, and get:

Sub Button130_Click()
MsgBox Worksheets("clients").CommandButton130.TopLeftCell.row
End Sub

I get "this object does not support this property or method"

I also tried changing the sub to CommandButton130_Click() and tried to
remove the
"command" from the lower line... Every combination of those two changes..

Suggestions?

Thank you!

Dave Peterson said:
Take a look at the .topleftcell of the controlbutton.

msgbox worksheets("sheet1").commandbutton1.topleftcell.row

Thomas Tremain wrote:

I have the need to use a control button to run a script, however, I
Click to expand...
need
the
script
to be able to tell the current position in the worksheet of that button.

The worksheet is sorted regularly, in many different ways, so I nned
Click to expand...
to
be
able to locate
at least the ROW where the button is located, because I will have a simular
button on
each row.

Any help is greatly appreciated!

news at thomas.tremaininc.com
www.LiveHost.net
www.GotoNames.com
www.TrafficExaminer.com
Click to expand...
Click to expand...
Click to expand...
 
Dave,

Thank you for all your assistance... You've been a wealth of knowledge!

I ended up doing it with control toolbox, and creating a ton of scripts, but
after
reading this reply, I'm going to redo part of this and simplify.

Thank you again!


Dave Peterson said:
Keep the button from the Forms toolbar and you'll be ok. Just assign each
button the same macro:

then you can get the topleftcell.row with something like:

msgbox activesheet.buttons(application.caller).topleftcell.row

The application.caller will return the name of the button that was clicked.

I find using the Forms version of the button easier to use when the macro is
going to do identical things.

====
You could also use the buttons from the control toolbox toolbar and have each
_click call a common macro and pass it the row. (but why bother if you already
have the buttons located?)


Thomas said:
That would explain why I couldn't find properties... I was creating a
command button from
the Forms Toolbar. It just wasn't working well for me.

Is it possible to create a commandbutton array (So I only have to write one
script instead of 200
identical?)

Thomas Tremain
www.LiveHost.net
www.GotoNames.com
www.TrafficExaminer.com

Dave Peterson said:
In another private reply:

try rightclicking on the button and looking in the name box (to the
Click to expand...
left
of the
formulabar).

But if this is a button from the control toolbox toolbar, then when you
rightclick on the button and select properties, you'll see the name in the
(name) box--right at the top of the list.


--
Dave Peterson
(e-mail address removed)


----- Original Message -----
From: "Thomas Tremain" <[email protected]>
To: "'Dave Peterson'" <[email protected]>
Sent: Monday, November 24, 2003 09:37
Subject: RE: Location of control button



I honestly cannot figure out how to get to the name of the button.

I can't just right click and go to properties... It's driving me nuts.

I have tried both names however..

Thomas Tremain
www.LiveHost.net
www.GotoNames.com
www.TrafficExaminer.com


-----Original Message-----
From: Dave Peterson [mailto:[email protected]]
Sent: Monday, November 24, 2003 6:26 AM
To: Thomas Tremain
Subject: Re: Location of control button


It looks like the button is called Button130, not commandbutton130.

And is it on the Clients worksheet?

--
Dave Peterson
(e-mail address removed)


----- Original Message -----
From: "Thomas Tremain" <[email protected]>
To: <[email protected]>
Sent: Monday, November 24, 2003 03:45
Subject: Re: Location of control button


Great! I just tried this, and get:

Sub Button130_Click()
MsgBox Worksheets("clients").CommandButton130.TopLeftCell.row
End Sub

I get "this object does not support this property or method"

I also tried changing the sub to CommandButton130_Click() and tried to
remove the
"command" from the lower line... Every combination of those two changes..

Suggestions?

Thank you!

Take a look at the .topleftcell of the controlbutton.

msgbox worksheets("sheet1").commandbutton1.topleftcell.row

Thomas Tremain wrote:

I have the need to use a control button to run a script, however,
Click to expand...
I
need
the
script
to be able to tell the current position in the worksheet of that button.

The worksheet is sorted regularly, in many different ways, so I
Click to expand...
nned
to
be
able to locate
at least the ROW where the button is located, because I will have a
simular
button on
each row.

Any help is greatly appreciated!

news at thomas.tremaininc.com
www.LiveHost.net
www.GotoNames.com
www.TrafficExaminer.com
Click to expand...
Click to expand...
Click to expand...
 
Back
Top