Can Shape be made unselectable?

shstrating

Board Regular
Joined
Sep 8, 2009
Messages
57
Excel 2016

Is it possible to make a Shape object unselectable but visible without Protecting the Worksheet/Workbook?

The reason I'm asking is that I've encountered the issue in which ActiveX controls such as Command Buttons, Text Boxes, Check Boxes, etc. change their size if a user is editing a workbook while having their computer connected to multiple monitors where at least one of the monitors is running non-native resolution (such as projecting to a conference room screen).

I've searched high and low across the web and discovered that this is a known issue. I've not been able to find a fix that is reported to work 100% of the time for 100% of the users.

Rather than fight a losing battle, I am now trying to find a way to mimic the look of an ActiveX Text Box and an ActiveX Check Box, and have the underlying Cell be used to enter either text content or an "X", respectively.

To mimic a Text Box I've created a Rectangle Shape (formatted with No Fill and a Border) as a 'picture frame' around a cell. I want the user to be able to click in the cell and type a value.

The problem is that the Shape is selectable and makes it difficult, if not almost impossible, to select the underlying Cell directly. They only sure way to get to the underlying Cell is to click into an adjacent Cell and arrow or Tab over to the desired Cell. This is a user annoyance and results in lots of complaints of the Form being unusable/unfriendly/piece of sh*t/etc.

Back to the original question:
Is it possible to make a Shape object unselectable but visible without Protecting the Worksheet/Workbook? (A VBA solution is fine, although I am not knowledgeable in VBA other than knowing enough to be EXTREMELY dangerous.)

Thanks for any help/hints/suggestions,
Steve
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
I don't know of a way to prevent an object being selected, but there may be better solutions. My first thought would be to set up a VBA User Form, which looks like a pop-up control panel (e.g. the Format Cell pop-up). I find these more robust than ActiveX objects placed directly on a worksheet, and they can look professional too, especially when working with multiple displays - in a presentation set-up, the controls can be on one screen whilst the Board are looking at the other, so you can change various parameters without people even seeing you do it

How many ActiveX objects do you have on your sheet, and what sort of thing are they doing?
 

shstrating

Board Regular
Joined
Sep 8, 2009
Messages
57
Sorry for the delayed response. Got busy at work and then bailed before checking for replies.

99.9% of the worksheet is just standard cells for data entry.

I'm using Check Boxes and a Text Box merely as a more polished looking way for users to check off one of two choices and enter a qty.

At the end of the day I can fall back on simply using bordered cells, which I should probably do anyway in the interest of usability.

I would still like to know, however, if Shapes can be made visible but not selectable for future reference.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,024
You could use controls from the Forms menu and link all of them to this Macro

Code:
Sub NotSelected()
    ActiveSheet.Shapes(Application.Caller).TopLeftCell.Select
End Sub

For command Buttons, you'd want something like

Code:
Sub Button1_Click()
    ActiveSheet.Shapes(Application.Caller).TopLeftCell.Select

    ' rest of code
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,054
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I would still like to know, however, if Shapes can be made visible but not selectable for future reference.
I don't know if this will solve the resizing problem for you or not, but for ActiveX controls (I'll use a TextBox for example purposes), use this GotFocus event code...
Code:
Private Sub TextBox1_GotFocus()
  Selection.Select
End Sub
It will immediately activate whatever cell or cells are currently selected.
 

shstrating

Board Regular
Joined
Sep 8, 2009
Messages
57
Thanks to all for the tips.

I'll have to wait until I'm back in the office Monday to try them out.

Hope you all have a great weekend.

Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,532
Messages
5,659,371
Members
418,499
Latest member
mbcmel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top