Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    Cincinnati
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Controls Disappear (2kSR1)

    I've created a "console" of controls that manipulate a graph on a sheet. The console consists of things like combo boxes that are placed over top of text boxes. The text boxes have text in them describing what the combo boxes do, and I can make the background color appealing, etc. The problem is this: even when the sheet is protected, if the user clicks on the background text box, the controls on top of the text box suddenly disappear. It is as if, when the text box is selected, it suddenly "moves to the front". The controls reappear as soon as you select something else. I'd like to set the text box so it can't be selected, but no such option appears in the properties box. How can I stop the user from selecting the text box? Or is there some other solution?

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Controls Disappear (2kSR1)

    As you have used a textbox to cover the underlying sheet I am assuming that the sheet is empty. If so the following will work.

    Rather than putting the text in a text box, place the text on the worksheet and format the background colour if desired.

    In VBA, in the ThisWorkbook section, add the following procedure, replacing "Sheet1" with your sheet name.
    The first line prevents the user from selecting any cells when the sheet is protected, the second line (optional) stops them scrolling away from the area with the controls. The user will still be able to use any activex controls or Form controls.

    Private Sub Workbook_Open()
    Sheets("Sheet1").EnableSelection = xlNoSelection
    Sheets("Sheet1").ScrollArea = "$a$1"
    End Sub

  3. #3
    Lounger
    Join Date
    Mar 2002
    Location
    Cincinnati
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controls Disappear (2kSR1)

    Thanks Tony. Your suggestion is a good one, but may not work out in this case. In fact the text box and controls are on a sheet with many other things on it. Those other things govern the layout of the grid of cells, so the text in those boxes, and more importantly the surrounding background color of the cells can't be adjusted to make them look right. But a text box can be manipulated independently, so that is why I was using one.

    Another interesting thing I discovered in looking at this: the Excel help says there is a property called TabStop for a text box. And it sounds like TabStop might just solve the problem. But despite what the help says, in fact the textbox control does not have such a property. I was thinking maybe I could use the Cancel option on the Enter event, but then realized that the Cancel only exists for the Exit Event. One final interesting discovery: if you disable the text box, the text color goes away and it becomes gray embossed. Even then you can still select the text box, which lights up the text in an ugly blue and causes all the superimposed controls to disappear, but if you move the cursor out of the text box, the controls all reappear and the ugly blue goes away even though you have not clicked anywhere else.

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controls Disappear (2kSR1)

    If you can protect the workbook then one work around would be to use the text box from the drawing toolbar rather than the one from the Control Toolbox. In MS's normal fashion they work differently <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    HTH

    Peter

  5. #5
    Lounger
    Join Date
    Mar 2002
    Location
    Cincinnati
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controls Disappear (2kSR1)

    Peter, you rang the bell.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •