Can Shape be made unselectable?

[shstrating]

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

 

[baitmaster]

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]

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]

You could use controls from the Forms menu and link all of them to this Macro

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

For command Buttons, you'd want something like

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

    ' rest of code
End Sub

 

[Rick Rothstein]

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...

Private Sub TextBox1_GotFocus()
  Selection.Select
End Sub

It will immediately activate whatever cell or cells are currently selected.

 

[shstrating]

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.