Solved

Show the Format Text Box or Format Autoshape dialog boxes with VBA

Posted on 2010-11-13
24
1,296 Views
Last Modified: 2012-06-22
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?
0
Comment
Question by:dsimcox
  • 10
  • 7
  • 6
  • +1
24 Comments
 
LVL 4

Accepted Solution

by:
BrainB earned 250 total points
ID: 34127749
try these .............
'=============================================================================

'- OPEN FORMAT CELLS DIALOG

'=============================================================================

Sub test1()

    SendKeys "^1", False

    SendKeys ("F"), True

End Sub

'=============================================================================





'=============================================================================

'- OPEN FORMAT AUTOSHAPE DIALOG

'=============================================================================

Sub test2()

    ActiveSheet.Shapes("AutoShape 2").Select

    SendKeys "^1", False

End Sub

'=============================================================================

Open in new window

0
 
LVL 10

Expert Comment

by:pkhari
ID: 34127826
Here is how you format a textbox:

Sub FormatTextBox()
Dim MyShape As Shape
Set MyShape = Sheet1.Shapes("Client")
    With MyShape.TextFrame.Characters(Start:=1, Length:=5).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Underline = True
    End With
End Sub

Hope this helps.

Cheers
P.K.
0
 

Author Comment

by:dsimcox
ID: 34127933
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.
0
 
LVL 4

Expert Comment

by:BrainB
ID: 34128248
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 (?)
0
 

Author Comment

by:dsimcox
ID: 34128286
Yes - I did select the shape first as follows:


 
Private Sub cmdFormatBox_Click()

Call ProtectMe(False)
ActiveSheet.Shapes(Me.txtGroupNameh.value).Select

SendKeys "^1", False

End Sub

Open in new window


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.
0
 
LVL 4

Expert Comment

by:BrainB
ID: 34128747
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 ?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34135636
You should be able to use:

application.commandbars.findcontrol(id:=791).execute


with either selected.
0
 

Author Comment

by:dsimcox
ID: 34136170
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)

 
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

Open in new window


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?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34136248
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
0
 

Author Comment

by:dsimcox
ID: 34136635
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
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34136661
Works fine for me.
0
 

Author Comment

by:dsimcox
ID: 34136738
Confusing! I'm getting the Run Time Error noted above. Using Excel 2003. There must be an Options setting that I'm missing.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34136758
What do you get if you run:

MsgBox Application.CommandBars.FindControl(ID:=791).Caption

in the Immediate Window?
0
 
LVL 4

Expert Comment

by:BrainB
ID: 34137400
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

'=============================================================================

Open in new window

0
 

Author Comment

by:dsimcox
ID: 34137488
rorya,

I get the same Run Time Error 91:  Object Variable or With Block Variable Not Set
when I run MsgBox Application.CommandBars.FindControl(ID:=791).Caption
in the Immediate Window.

BrainB:
I'm not trying to select the AutoShape - I'm trying to open a dialog box.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34137521
OK, so what do you get for:
?application.commandbars(1).controls("Format").Controls(1).Id
0
 
LVL 4

Expert Comment

by:BrainB
ID: 34137687
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.
0
 

Author Comment

by:dsimcox
ID: 34138412
rorya:

?application.commandbars(1).controls("Format").Controls(1).Id
 855

I tried replacing ID Code 791 with 855 and got
 MyCode

================================================

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
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34138577
You need to check the Id with the textbox selected.
0
 

Author Comment

by:dsimcox
ID: 34140710
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:
Private Sub cmdFormatBox_Click()

Call ProtectMe(False)
ActiveSheet.Shapes(Me.txtGroupNameh.value).Select
Application.CommandBars.ExecuteMso ("ObjectFormatDialog")

End Sub

Open in new window


Unfortunately, I need a solution for Excel 2003, since my internal "customers" all use that version.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34140757
Not with the dialog open, just with the textbox selected. :)
0
 

Author Comment

by:dsimcox
ID: 34141003
Sorry - I misunderstood.

?application.commandbars(1).controls("Format").Controls(1).Id
 855

Open in new window

0
 

Author Closing Comment

by:dsimcox
ID: 34149247
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!
0
 
LVL 4

Expert Comment

by:BrainB
ID: 34150439
Glad it worked out in the end !!!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem to open Excel file 15 44
Excel  Worksheet Relationships 7 25
Calculate days between two dates 7 38
Macro Filter fix 3 13
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now