PDA

View Full Version : [SOLVED] Toggle "select objects" instead of cells ??



ksor
01-17-2018, 05:57 AM
I want to set Excel to "select shapes" instead of cells in the bar - I've tried this:

Call CommandBars("Drawing").Controls("Select Objects").Execute

but it wont compile at all !

How is it done ?

p45cal
01-17-2018, 06:27 AM
Drop the Call.

ksor
01-17-2018, 06:39 AM
Yeah, yeah, ... error 91 Object not set !

It's Excel 2016 Home365 version

p45cal
01-17-2018, 06:44 AM
All right - I'm only on Excel 2010 and it works fine here.
Perhaps show more of your code.

ksor
01-17-2018, 06:56 AM
There is no more code ... I just want to secure the selection is on SHAPES in the first place.

I place the line in Workbook_Open

p45cal
01-17-2018, 07:42 AM
Test the attached.
If it errors or doesn't work, in the macro blah, try enabling all the commented-out lines It will try for 5 seconds before giving up.
I'm guessing Excel hasn't fully loaded/isn't quite ready when the original Workbook_Open code was running.

ksor
01-17-2018, 08:00 AM
No Cure no pay !

Nothing happens .... the "Select Objects" is NOT toggled ON

p45cal
01-17-2018, 11:28 AM
It is here!
Is there any code running anywhere else - for example another event code?

ksor
01-17-2018, 12:05 PM
No code running anywhere !

I think we are talking past each other - right ?

What I'm looking for is enabling the user to select SHAPES flowting on the worksheet by "lassoing" around them - normaly this will select a bunch of cells UNLESS you have toggled this "Select Objects" in the ribbon/bar !

p45cal
01-17-2018, 12:17 PM
Oh hold on, .Execute is a toggle command (it can switch the mode on AND off). Try the attached in the same way as msg#6.

ps. yes I've understood this:
What I'm looking for is enabling the user to select SHAPES flowting on the worksheet by "lassoing" around them - normaly this will select a bunch of cells UNLESS you have toggled this "Select Objects" in the ribbon/bar !

ksor
01-17-2018, 12:39 PM
It's simply NOT working

p45cal
01-17-2018, 12:41 PM
By the way, this is important: Supply links to everywhere you've cross posted this to. It's a rule here and at most other forums including MrExcel.
Why? For your own benefit ultimately, have a careful read of http://www.excelguru.ca/content.php?184

p45cal
01-17-2018, 12:43 PM
It's simply NOT working

I'm running out of ideas.
Do you have access to Excel2010 somewhere so that you can test whether it's simply down to version differences?

Aflatoon
01-18-2018, 02:30 AM
Try this instead:


application.CommandBars.ExecuteMso "ObjectsSelect"

ksor
01-18-2018, 03:41 AM
:hi: Nice ... thx, it works nicely !

Just inserted in the Workbook_Open sub !

ksor
01-18-2018, 07:15 AM
Oh, not working !

Next time I opened the file I got error

21389

:banghead:

p45cal
01-18-2018, 08:23 AM
review msg#12

ksor
01-18-2018, 08:40 AM
No solution there, I guess !

p45cal
01-18-2018, 09:22 AM
I guess you're doing this on purpose
Supply links to…

ksor
01-18-2018, 10:16 AM
???????

I don't get you !

p45cal
01-18-2018, 11:12 AM
Please read the FAQ, especially this part (http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3) (multiposting) as well as the link within it.
I'm not a moderator here so I shouldn't be back-seat moderating, I'll try to get a moderator to explain.

Jacob Hilderbrand
01-18-2018, 11:27 AM
If you cross post to different forums you need to place a link to it. And should do this on all sites so people can see if a solution has already been found.

Cross Post - https://www.mrexcel.com/forum/excel-questions/1039303-toggle-select-objects-instead-cells.html

p45cal
01-19-2018, 06:28 AM
If there are no selectable shapes on the activesheet at the time the command is executed it will not go into that mode and may generate an error, so you can do one of the following:

ensure the sheet's shapes.count > 0 before executing the command
activate a sheet you know has shapes on first
add a shape to the sheet before executing the command

ksor
01-19-2018, 09:33 AM
If there are no selectable shapes on the activesheet at the time the command is executed it will not go into that mode and may generate an error, so you can do one of the following:

ensure the sheet's shapes.count > 0 before executing the command
activate a sheet you know has shapes on first
add a shape to the sheet before executing the command



:thumb:thumb THAT's IT !

Thx a lot !

p45cal
01-19-2018, 11:52 AM
:thumb:thumb THAT's IT !That's excellent. Now it would be netiquette to go to your post/thread at MrExcel and tell them that's it's been solved, and how, referring them here if you want (actually their rules say you should refer them to here).
Not only would this save others wasting their time on your problem, but would also inform others how it was solved.
How about it?

ksor
01-19-2018, 11:12 PM
Here is the solution:


Private Sub Workbook_Open()
If ActiveSheet.Shapes.Count > 0 Then Application.CommandBars.ExecuteMso "ObjectsSelect"
End Sub



AND a link is mad e at Mr. Excel ...