dsimcox
asked on
Show the Format Text Box or Format Autoshape dialog boxes with VBA
I need to be able to open the Format Text Box and Format Autoshape dialog boxes from Excel (2003) VBA. (these dialog boxes are accessed manually by right-clicking the Shape object)
Application.Dialogs does not appear to include references to these objects. There must be another class that includes them but I'm unable to reference it.
Is there another way?
Application.Dialogs does not appear to include references to these objects. There must be another class that includes them but I'm unable to reference it.
Is there another way?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brian - your on the right track, but the test2 code <SendKeys "^1", False>
is not working.
test1 code is for the wrong dialog box. I want to open the Shapes Format Text Box - not the Cells Format Dialog Box. Shapes Format Text Box opens when you right click a shape and choose "Format Text Box..."
========================== ========== ========== =======
pkhari - I know how to do what you've suggested. I want to be able to open "built-in' dialog boxes via VBA by clicking a cmd button.
is not working.
test1 code is for the wrong dialog box. I want to open the Shapes Format Text Box - not the Cells Format Dialog Box. Shapes Format Text Box opens when you right click a shape and choose "Format Text Box..."
==========================
pkhari - I know how to do what you've suggested. I want to be able to open "built-in' dialog boxes via VBA by clicking a cmd button.
To get that dialog we must select a shape first. The menu contents then change accordingly.
Try it manually to see what I mean.
I cannot see why you would want to use that dialog with no shape selected (?)
Try it manually to see what I mean.
I cannot see why you would want to use that dialog with no shape selected (?)
ASKER
Yes - I did select the shape first as follows:
I want the user to be able to access this dialog box via a button, because the worksheet is protected. I unprotect it prior to executing your code.
Private Sub cmdFormatBox_Click()
Call ProtectMe(False)
ActiveSheet.Shapes(Me.txtGroupNameh.value).Select
SendKeys "^1", False
End Sub
I want the user to be able to access this dialog box via a button, because the worksheet is protected. I unprotect it prior to executing your code.
I am using Excel 2003 so it may be different.
What is the keyboard method ? i.e. select as shape and look in the "Format" menu.
What shortcut key(s) does it show at the right?
In 2003 it's the same Ctrl+1 for cell or shape depending what is selected at the time. The menu changes description.
Otherwise, what letter key is underlined ?
What is the keyboard method ? i.e. select as shape and look in the "Format" menu.
What shortcut key(s) does it show at the right?
In 2003 it's the same Ctrl+1 for cell or shape depending what is selected at the time. The menu changes description.
Otherwise, what letter key is underlined ?
You should be able to use:
application.commandbars.fi ndcontrol( id:=791).e xecute
with either selected.
application.commandbars.fi
with either selected.
ASKER
rorya - this is the kind of solution I am looking for. However I'm getting a Run Time Error 91: Object Variable or With Block Variable not set. using this code: (first code block example)
Note that I am able to show the Format Text dialog box using a similar method - Application.Dialogs, (second code block example) and that is working just fine.
How did you find the ID:=791 for the Format Text dialog box?
Private Sub cmdFormatBox_Click()
Call ProtectMe(False)
ActiveSheet.Shapes(Me.txtGroupNameh.value).Select
Application.CommandBars.FindControl(ID:=791).Execute
End Sub
Private Sub cmdFormatText_Click()
Call ProtectMe(False)
ActiveSheet.Shapes(Me.txtGroupNameh.value).Select
Application.Dialogs(xlDialogFormatFont).Show
End Sub
Note that I am able to show the Format Text dialog box using a similar method - Application.Dialogs, (second code block example) and that is working just fine.
How did you find the ID:=791 for the Format Text dialog box?
Can you post a sample workbook? The code works fine for me in 2003.
I got the 791 by checking the ID for the first control in the Format dropdown of the main menu bar using:
?application.commandbars(1 ).controls ("Format") .Controls( 1).Id
I got the 791 by checking the ID for the first control in the Format dropdown of the main menu bar using:
?application.commandbars(1
ASKER
OK - Here's a sample workbook for you to examine. You can show a userform by pressing the keyboard shortcut "CTRL + SHIFT + m" ShowTextFormatDialog.xls
Works fine for me.
ASKER
Confusing! I'm getting the Run Time Error noted above. Using Excel 2003. There must be an Options setting that I'm missing.
What do you get if you run:
MsgBox Application.CommandBars.Fi ndControl( ID:=791).C aption
in the Immediate Window?
MsgBox Application.CommandBars.Fi
in the Immediate Window?
So this is the code then
'=============================================================================
'- OPEN FORMAT AUTOSHAPE DIALOG
'=============================================================================
Sub test2()
ActiveSheet.Shapes("AutoShape 2").Select
'- Ctrl = Shift + M
SendKeys "^+M", False
End Sub
'=============================================================================
ASKER
rorya,
I get the same Run Time Error 91: Object Variable or With Block Variable Not Set
when I run MsgBox Application.CommandBars.Fi ndControl( ID:=791).C aption
in the Immediate Window.
BrainB:
I'm not trying to select the AutoShape - I'm trying to open a dialog box.
I get the same Run Time Error 91: Object Variable or With Block Variable Not Set
when I run MsgBox Application.CommandBars.Fi
in the Immediate Window.
BrainB:
I'm not trying to select the AutoShape - I'm trying to open a dialog box.
OK, so what do you get for:
?application.commandbars(1 ).controls ("Format") .Controls( 1).Id
?application.commandbars(1
Do you not have an shortcut key equivalent at all ? It seems very odd.
When I select a your textbox and open the Format menu the top item says
TextBox Ctrl +1
as the shortcut keys to open the dialog you want.
With a cell selected it shows 'Cells Ctl+1'
This is in Excel 2003 though.
When I select a your textbox and open the Format menu the top item says
TextBox Ctrl +1
as the shortcut keys to open the dialog you want.
With a cell selected it shows 'Cells Ctl+1'
This is in Excel 2003 though.
ASKER
rorya:
?application.commandbars(1 ).controls ("Format") .Controls( 1).Id
855
I tried replacing ID Code 791 with 855 and got
========================== ========== ========== ==
BrainB:
Yes - If I select the textbox and manually go to the Format Menu, it does show the caption TextBox Ctrl + 1. If I run <SendKeys "^1", False> via the ClickEvent for the command button, or try to run it from the Immediate Window, it produces nothing - no messages, no dialog box. I also tried hiding the userform - and unloading it before executing SendKeys, but no difference
?application.commandbars(1
855
I tried replacing ID Code 791 with 855 and got
==========================
BrainB:
Yes - If I select the textbox and manually go to the Format Menu, it does show the caption TextBox Ctrl + 1. If I run <SendKeys "^1", False> via the ClickEvent for the command button, or try to run it from the Immediate Window, it produces nothing - no messages, no dialog box. I also tried hiding the userform - and unloading it before executing SendKeys, but no difference
You need to check the Id with the textbox selected.
ASKER
rorya,
I'm unable to execute a command to get the ID in the Immediate window with the Format Textbox dialog box open.
The following code works perfectly in Excel 2007:
Unfortunately, I need a solution for Excel 2003, since my internal "customers" all use that version.
I'm unable to execute a command to get the ID in the Immediate window with the Format Textbox dialog box open.
The following code works perfectly in Excel 2007:
Private Sub cmdFormatBox_Click()
Call ProtectMe(False)
ActiveSheet.Shapes(Me.txtGroupNameh.value).Select
Application.CommandBars.ExecuteMso ("ObjectFormatDialog")
End Sub
Unfortunately, I need a solution for Excel 2003, since my internal "customers" all use that version.
Not with the dialog open, just with the textbox selected. :)
ASKER
Sorry - I misunderstood.
?application.commandbars(1).controls("Format").Controls(1).Id
855
ASKER
Brian - I got your solution to work. Apparently, the reason it was not working for me originally, is that there cannot be another userform open (modal or non-modal) - so the anticipated menu would not open unless the userform is hidden or unloaded. Also - the menu will not show if there is a break in the VBA code - i.e. you cannot use this method to open a commandbar object while you are stepping through the code for some reason.
Thanks for your assist on this!
Thanks for your assist on this!
Glad it worked out in the end !!!
Sub FormatTextBox()
Dim MyShape As Shape
Set MyShape = Sheet1.Shapes("Client")
With MyShape.TextFrame.Characte
.Name = "Arial"
.FontStyle = "Bold"
.Underline = True
End With
End Sub
Hope this helps.
Cheers
P.K.