CommandButton Address in Worksheet?

backup69

Active Member
Joined
Jan 20, 2004
Messages
271
How i can lock CommandButton with cell
and put button name in this cell
i dont know... something like this :)
Range(Commandbutton1.Address) = CommandButton1.Caption

Second questions. How i can take and use commandbutton address
in the UserForm something like this
adr = Worksheets(1).CommandButton1.Address

???
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

backup69

Active Member
Joined
Jan 20, 2004
Messages
271
i think i asked stupid questions?
anyone tell me "yes u stupid". :wink:
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
hi!
command buttons do not have address!
if you mean location of the command button, it is define by
its top and left property.
but if you mean its index it is define in the shapes index

try this one. this will give you all the names of the shapes in the active sheet.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
<SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Shapes.Count
MsgBox Shapes(i).Name
<SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

backup69

Active Member
Joined
Jan 20, 2004
Messages
271
ty but i dont mean this.
If i look in format control/propertis there function (move and size with cell)
I thougt that in that case the location of the button will be in a cell.
Maybe it would be correct to keep the name of the button in a cell under the button, and always when the name of the button changes, change the value in that cell aswell.
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883

ADVERTISEMENT

Hi!
how will you change the name of the button? by code? or manual? or you mean the caption of the button not the name itself? the name could be different with the caption!
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
im not really sure if what you are thinking of.
If you think to change the caption each time you change a cell

then try this!

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">If</SPAN> Target = Range("a1") <SPAN style="color:#00007F">Then</SPAN>
    CommandButton1.Caption = Range("a1")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


but if you wan to change the caption of the button then
have it reflected on a cell, its no use of knowing the details of the button.
just say

commandbutton1.caption="hello"
range("a1").value="hello"

I dont think that this is what you want!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,090
Messages
5,545,893
Members
410,711
Latest member
Josh324
Top