CLARIFICATION: accessing a control (button) on a worksheet
Marissa Treinen
I'll try again.
What I want to know is if I can access a control on a worksheet in VBA
CODE.
I have code in Workbook1 that loads another workbook (Workbook2) from a
template. Workbook2 has a button on sheet1 (button1) from the template. I
have the workbook2's object (wkbk as workbook) and the worksheet object
(wksht as worksheet) for the worksheet in workbook2. Can I then use wkbk or
wksht or something like that in workbook1's code to access the button and
move it?
I was thinking it would be along the lines of:
wksht.Controls(button1).top = 5
or
wkbk.controls(button1).top = 5
but can't find anything remotely like this.
Thanks
Marissa
Chris Kemp
Marissa Treinen wrote in message ...
>I don't think I made myself clear last time after reading the responses so
>I'll try again.
>
> What I want to know is if I can access a control on a worksheet in VBA
>CODE.
Here's what I have donw from Access using access basic code:
Dim opt As Object
Set oleObj(i) = oApp.ActiveSheet.OLEObjects.Add( _ 'oapp
is Excel object
ClassType:="Forms.OptionButton.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=Range(Mid$(sheetcolrow, 2)).Left, _
Top:=Range(Mid$(sheetcolrow, 2)).Top, _
Width:=Range(Mid$(sheetcolrow, 2)).Width, _
Height:=Range(Mid$(sheetcolrow, 2)).Height)
Set opt = oleObj(i).Object
oleObj(i).Visible = True
opt.Caption = token
opt.GroupName= Mid$(sheetcolrow, 1, 1) + Mid$(sheetcolrow, 3)
opt.BackColor = &H80000005
oleObj(i).LinkedCell = Range(Mid$(sheetcolrow, 2)). _
Address(False, False, xlA1, True)
Robert Rosenberg
Sub MoveButton()
Dim wksht As Worksheet
Set wksht = Workbooks("Book1").Worksheets("sheet1")
With wksht.Shapes("Button 1")
.Top = .Parent.Rows(5).Top
.Left = .Parent.Columns(5).Left
End With
End Sub
Your button is a shape. Whether your button was created using via the Forms
toolbar or the Control Toolbox, you can find out what the name of the object
is by selecting it and viewing it's name in the Name box (left side of the
Formula Bar and just above the Row indicators).
Instead of assigning hard-coded values of 5 to the top & left properties, I
chose to anchor the shape to a specified row and column.
______________________
Robert Rosenberg
RCOR Consulting
Microsoft MVP - Excel
Marissa Treinen wrote in message ...
>I don't think I made myself clear last time after reading the responses so
>I'll try again.
>
> What I want to know is if I can access a control on a worksheet in VBA
>CODE.
>
Robert Rosenberg
properties...
1. Right-Click any toolbar button and choose the Visual Basic listing on the
shortcut menu...
2. Once the toolbar appears, click the Design Mode button (protractor image
with a pencil on it)...
3. Now you can edit the object.
Click & drag it to move it around. Right-Click it and choose Properties (or
select it and hit F4). Locate the TakeFocusOnClick property and turn it off.
______________________
Robert Rosenberg
RCOR Consulting
Microsoft MVP - Excel
John de Beer wrote in message <3681d404.54521376@news>...
>
>>Your button is a shape. Whether your button was created using via the
Forms
>>toolbar or the Control Toolbox, you can find out what the name of the
object
>>is by selecting it and viewing it's name in the Name box (left side of the
>>Formula Bar and just above the Row indicators).
>
>sorry to buut in, but I have a very weird problem. I have a
>commandbutton on a spreadsheet (sheet14). It was put on the
>spreadsheet with the control toolbox. Now I cannot do anything with
>this control button. It seems to take focus, but does not respond to
>a right click. I can find a reference to it on sheet14 in VBA.Project
>but no where else. How can I get rid of this offending button.
>
>thanks
>
>john de beer
>
>
>
John de Beer
Chris Kemp
John de Beer wrote in message <3681d404.54521376@news>...
>sorry to buut in, but I have a very weird problem. I have a
>commandbutton on a spreadsheet (sheet14). It was put on the
>spreadsheet with the control toolbox. Now I cannot do anything with
>this control button. It seems to take focus, but does not respond to
>a right click. I can find a reference to it on sheet14 in VBA.Project
>but no where else. How can I get rid of this offending button.
go into design mode by right clicking on object, and choosing VB. then go
into design mode (the button with triangle). once in design mode, left
click on object and delete the little sucker.
John de Beer
These newsgroups alone are worth the price of admission to Internet.
Thanks so much for your time and clear advice.
John de Beer
On Wed, 23 Dec 1998 23:23:56 -0800, "Robert Rosenberg"
<Bla...@email.msn.com> wrote:
>You need to switch to design mode to select the control and work with its
>properties...
>
>1. Right-Click any toolbar button and choose the Visual Basic listing on the
>shortcut menu...
>
>2. Once the toolbar appears, click the Design Mode button (protractor image
>with a pencil on it)...
>
>3. Now you can edit the object.
>
>Click & drag it to move it around. Right-Click it and choose Properties (or
>select it and hit F4). Locate the TakeFocusOnClick property and turn it off.
>______________________
>Robert Rosenberg
>RCOR Consulting
>Microsoft MVP - Excel
>
>
>http://ntware.com
>
>
>
>John de Beer wrote in message <3681d404.54521376@news>...
>>
>>>Your button is a shape. Whether your button was created using via the
>Forms
>>>toolbar or the Control Toolbox, you can find out what the name of the
>object
>>>is by selecting it and viewing it's name in the Name box (left side of the
>>>Formula Bar and just above the Row indicators).
>>
>>sorry to buut in, but I have a very weird problem. I have a
>>commandbutton on a spreadsheet (sheet14). It was put on the
>>spreadsheet with the control toolbox. Now I cannot do anything with
>>this control button. It seems to take focus, but does not respond to
>>a right click. I can find a reference to it on sheet14 in VBA.Project
>>but no where else. How can I get rid of this offending button.
>>
>>thanks
>>
>>john de beer
>>
>>
>>
>
>