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.ro w

Thomas Tremain wrote:

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

--

Dave Peterson

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" wrote in message
...
Take a look at the .topleftcell of the controlbutton.

msgbox worksheets("sheet1").commandbutton1.topleftcell.ro w

Thomas Tremain wrote:

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

--

Dave Peterson

In a private reply:

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

And is it on the Clients worksheet?


Thomas Tremain wrote:

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" wrote in message
...
Take a look at the .topleftcell of the controlbutton.

msgbox worksheets("sheet1").commandbutton1.topleftcell.ro w

Thomas Tremain wrote:

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

--

Dave Peterson


--

Dave Peterson

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



----- Original Message -----
From: "Thomas Tremain"
To: "'Dave Peterson'"
Sent: Monday, November 24, 2003 09:37
Subject: 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 ]
Sent: Monday, November 24, 2003 6:26 AM
To: Thomas Tremain
Subject: Location of control button


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

And is it on the Clients worksheet?

--
Dave Peterson



----- Original Message -----
From: "Thomas Tremain"
To:
Sent: Monday, November 24, 2003 03:45
Subject: 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" wrote in message
...
Take a look at the .topleftcell of the controlbutton.

msgbox worksheets("sheet1").commandbutton1.topleftcell.ro w

Thomas Tremain wrote:

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

--

Dave Peterson

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



----- Original Message -----
From: "Thomas Tremain"
To: "'Dave Peterson'"
Sent: Monday, November 24, 2003 09:37
Subject: 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 ]
Sent: Monday, November 24, 2003 6:26 AM
To: Thomas Tremain
Subject: Location of control button


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

And is it on the Clients worksheet?

--
Dave Peterson



----- Original Message -----
From: "Thomas Tremain"
To:
Sent: Monday, November 24, 2003 03:45
Subject: 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" wrote in message
...
Take a look at the .topleftcell of the controlbutton.

msgbox worksheets("sheet1").commandbutton1.topleftcell.ro w

Thomas Tremain wrote:

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

--

Dave Peterson

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).topleftcel l.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 Tremain wrote:

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



----- Original Message -----
From: "Thomas Tremain"
To: "'Dave Peterson'"
Sent: Monday, November 24, 2003 09:37
Subject: 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 ]
Sent: Monday, November 24, 2003 6:26 AM
To: Thomas Tremain
Subject: Location of control button


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

And is it on the Clients worksheet?

--
Dave Peterson



----- Original Message -----
From: "Thomas Tremain"
To:
Sent: Monday, November 24, 2003 03:45
Subject: 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" wrote in message
...
Take a look at the .topleftcell of the controlbutton.

msgbox worksheets("sheet1").commandbutton1.topleftcell.ro w

Thomas Tremain wrote:

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

--

Dave Peterson


--

Dave Peterson

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" wrote in message
...
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).topleftcel l.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 Tremain wrote:

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



----- Original Message -----
From: "Thomas Tremain"
To: "'Dave Peterson'"
Sent: Monday, November 24, 2003 09:37
Subject: 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 ]
Sent: Monday, November 24, 2003 6:26 AM
To: Thomas Tremain
Subject: Location of control button


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

And is it on the Clients worksheet?

--
Dave Peterson



----- Original Message -----
From: "Thomas Tremain"
To:
Sent: Monday, November 24, 2003 03:45
Subject: 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" wrote in message
...
Take a look at the .topleftcell of the controlbutton.

msgbox worksheets("sheet1").commandbutton1.topleftcell.ro w

Thomas Tremain wrote:

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

--

Dave Peterson


--

Dave Peterson