Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt user to select a shape
Hi,
You can use the InputBox to prompt the user to enter some text or range - is it possible to also prompt the user to select a shape? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt user to select a shape
Sub shapepicker()
x = Application.InputBox(prompt:="enter the shape name: ", Type:=2) ActiveSheet.Shapes(x).Select End Sub The user would enter something like Oval 1 -- Gary''s Student - gsnu200800 "MM User" wrote: Hi, You can use the InputBox to prompt the user to enter some text or range - is it possible to also prompt the user to select a shape? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt user to select a shape
Apologies but I need the user to actually select the shape not enter the
name - is this possible? thanks "Gary''s Student" wrote in message ... Sub shapepicker() x = Application.InputBox(prompt:="enter the shape name: ", Type:=2) ActiveSheet.Shapes(x).Select End Sub The user would enter something like Oval 1 -- Gary''s Student - gsnu200800 "MM User" wrote: Hi, You can use the InputBox to prompt the user to enter some text or range - is it possible to also prompt the user to select a shape? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt user to select a shape
If you mean by click the shape. We will use two macros:
Sub pic_click() MsgBox (Application.Caller) 'do something else End Sub Sub tell_user() MsgBox ("please click on a shape") End Sub Both macros go in standard modules. Right-click all the shapes and Assign Macro... All shapes are to be assigned the pic_click macro 1. first run tell_user. The user will first click the ok button and then click on a shape 2. this causes the pic_click macro to run. Application.Caller tells the user (and your VBA) exactly which shape was clicked. Putting macros on Shapes is just as effective as putting them on Buttons and a lot more fun! -- Gary''s Student - gsnu2007k "MM User" wrote: Apologies but I need the user to actually select the shape not enter the name - is this possible? thanks "Gary''s Student" wrote in message ... Sub shapepicker() x = Application.InputBox(prompt:="enter the shape name: ", Type:=2) ActiveSheet.Shapes(x).Select End Sub The user would enter something like Oval 1 -- Gary''s Student - gsnu200800 "MM User" wrote: Hi, You can use the InputBox to prompt the user to enter some text or range - is it possible to also prompt the user to select a shape? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt user to select a shape
Maybe you could show a dialog that allows the user to choose a shape (or
multiple shapes). In xl2003, there is an icon that you can add to your favorite toolbar. You can do it via: tools|customize|commands tab|drawing category look for "Select multiple objects" In code, you can do this: Option Explicit Sub testme01() Dim myCtrl As CommandBarControl Dim myCB As CommandBar Dim iCtr As Long Set myCB = Nothing Set myCtrl = Nothing On Error Resume Next Set myCtrl = Application.CommandBars.FindControl(ID:=3990) On Error GoTo 0 If myCtrl Is Nothing Then 'not on any existing toolbar, so create a temporary toolbar Set myCB = Application.CommandBars.Add(temporary:=True) myCB.Visible = False 'add that button Set myCtrl = myCB.Controls.Add(Type:=msoControlButton, ID:=3990) End If myCtrl.Execute MsgBox TypeName(Selection) Select Case LCase(TypeName(Selection)) Case Is = LCase("Range") MsgBox "A range is selected" Case Else For iCtr = 1 To Selection.ShapeRange.Count MsgBox Selection.ShapeRange(iCtr).Name Next iCtr End Select 'clean up If myCB Is Nothing Then 'found it on an existing toolbar Else myCB.Delete End If End Sub ========= Personally, I think I'd tell the users to select the object before the code starts. You can test the typename() of the selection to see if they have a Range selected. If it is a Range, just yell at the user to select an object and start the macro once more. MM User wrote: Hi, You can use the InputBox to prompt the user to enter some text or range - is it possible to also prompt the user to select a shape? Thanks -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt user to select a shape
Thanks Dave and Gary,
Both options are suitable and I will probably use a bit of both - thanks! "Dave Peterson" wrote in message ... Maybe you could show a dialog that allows the user to choose a shape (or multiple shapes). In xl2003, there is an icon that you can add to your favorite toolbar. You can do it via: tools|customize|commands tab|drawing category look for "Select multiple objects" In code, you can do this: Option Explicit Sub testme01() Dim myCtrl As CommandBarControl Dim myCB As CommandBar Dim iCtr As Long Set myCB = Nothing Set myCtrl = Nothing On Error Resume Next Set myCtrl = Application.CommandBars.FindControl(ID:=3990) On Error GoTo 0 If myCtrl Is Nothing Then 'not on any existing toolbar, so create a temporary toolbar Set myCB = Application.CommandBars.Add(temporary:=True) myCB.Visible = False 'add that button Set myCtrl = myCB.Controls.Add(Type:=msoControlButton, ID:=3990) End If myCtrl.Execute MsgBox TypeName(Selection) Select Case LCase(TypeName(Selection)) Case Is = LCase("Range") MsgBox "A range is selected" Case Else For iCtr = 1 To Selection.ShapeRange.Count MsgBox Selection.ShapeRange(iCtr).Name Next iCtr End Select 'clean up If myCB Is Nothing Then 'found it on an existing toolbar Else myCB.Delete End If End Sub ========= Personally, I think I'd tell the users to select the object before the code starts. You can test the typename() of the selection to see if they have a Range selected. If it is a Range, just yell at the user to select an object and start the macro once more. MM User wrote: Hi, You can use the InputBox to prompt the user to enter some text or range - is it possible to also prompt the user to select a shape? Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Prompt a user to select a Folder. | Excel Programming | |||
Prompt user to select directory | Excel Programming | |||
Prompt user to select a printer using a checkbox within a user | Excel Programming | |||
how do I prompt the user to select a cell? | Excel Programming | |||
Prompt user to select file with default file selected dialog | Excel Programming |