Preventing user typing in cells 9

[Stretchwickster]

Hi people!
Is it possible to prevent a user from typing into a cell in Excel VBA?
Also, is it possible to detect when they are typing in that cell, i.e. to trigger the showing of a message box?

Your help would be much appreciated! Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[arst06d]

Dont need VBA - Excel takes care of it.

select the cells you want to prevent the user changing
Click Format > Cells
on the protection tab make sure the locked checbox is checked
Click OK
Click Tools > Protection > Protect Sheet (can enter a password if required)

The selected cells will not be editable after this - a message will be automatically displayed.

 

[Stretchwickster]

Let me clarify the problem a bit better. I've got a form in which the user enters certain data. When I click the "OK" button on this form, the data they entered is inserted into the appropriate cells. It is these cells I need to prevent the user typing data directly into.

Although locking the cells prevents the user from typing data in manually - it also prevents my code from inserting data into the cells.

So is there like an OnKeyPress event for the cells? Or is the solution to programmatically turn off protection, insert the data, then turn on protection again?

Also, is there a way to display my own custom message box rather than the Excel one, when the user tries to type in the protected cells? Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[arst06d]

yep - manipulate protection just long enough for your code to change the data.

Haven't come across a way to display own message.

 

[Zathras]

Use something like the following to protect:

Sub lockit()
  Application.Worksheets(1).Protect UserInterfaceOnly:=True
End Sub

That allows the program to update locked cells, but not the user.

AFAIK there's no way to over-ride Excel's error message box in this situation.

 

[Stretchwickster]

Hey Zathras - I can't get away from you! ;-)

Can you apply this Protect method to a range of cells?
Another thing I forgot to mention was that a certain range must allow user-entered data and another range mustn't, within one worksheet!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Stretchwickster]

Zathras,

So there's no way to intercept the message which generates the Excel message box? Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[xlbo]

Unfortunately - UserInterfaceOnly refers to the whole sheet (unless this has been changed for >97 versions)
If the cells are unlocked however, the user can enter data to their heart's content ;-)
Therefore solution =
Use UserInterfaceOnly:=True (set this in workbook_OPEN event as it resets when the wb is closed)
Make sure that the cells where user enters data directly into worksheet are unlocked Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Zathras]

...Can you apply this Protect method to a range of cells?
...

Not sure what you mean. Protection works by unlocking cells you want the user to be able to update and then locking the sheet. Feels a little backwards at first, but then that's Microsoft for you. Select the cells to unlock and then Format/Cells... Protection and uncheck the "Locked" box. Repeat for other ranges. Finally, Tools/Protection/Protect Sheet...

The UserInterfaceOnly property allows the programmer to do what you want to do, i.e. update a locked cell from VBA without having to unprotect the sheet first. AFAIK it has to be done thru code. One usually uses the Workbook_Open event for that sort of thing.

Sorry about the message box, but the user has to click into the cell first before trying to type into it. When the sheet is protected, the tab key moves directly from unlocked cell to another unlocked cell. So it's really not all that bad.

 

[Stretchwickster]

Is workbook_OPEN the same as Autpen cos that's where I've got it at the moment? Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Zathras]

Workbook_Open is an event. You put event-handlers on the code page for the object to which the event pertains.

In this case, when you double-click in the left-hand pane on the line where it says "ThisWorkbook" (or right-click and select "View Code&quot the code page is opened. You should see something like "Book1 - ThisWorkbook (Code)" in the title bar. There are two combo boxes at the top. Select Workbook from the left-hand box, then the code stub for Workbook_Open is set up for you. Other events can be selected from the right-hand box.

So when you are finished you should see code like this:

Private Sub Workbook_Open()
  Application.Worksheets(1).Protect UserInterfaceOnly:=True
End Sub

AFAIK, this has exactly the same effect as coding a macro with the name Autpen on a code page. But I think the existence of Autpen is only for backward compatibility with older spreadsheets (e.g., Lotus 1-2-3). IMHO, handling the Workbook_Open event is to be preferred. (Try to find "Autpen" in the help file. It's there, but kind of buried.) Anyway, it's more Delphi-like .

 

[Stretchwickster]

Cheers Zathras - I assumed I had to double-click ThisWorkbook but I didn't change the left-hand combo-box to Workbook, I was clicking the righ-hand combo-box and finding it empty - so cheers for the clarification on that! As usual you deserve a star for your extra-Delphi activities!

Cheers xlbo for your help!

Cheers for the quick reply arst06d! Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[xlbo]

Zathras - fully agree with using OPEN EVENT rather than auto_open for most cases HOWEVER, it is a legacy of excel 95 (which still used dialog boxes rather than forms etc and had NO EVENTS at all)
Also, there is a method of the workbook which is RUNAUTOMACROS - this is also in there for backwards compatability but it is VERY useful when dealing with workbooks that open other workbooks. As I'm sure you are aware, open events and auto_open macros do NOT fire when the wb is openend via code. Therefore the easiest way to make these fire is to have them in Auto_open subs and use the workbook.runautomacros method. Well, that's my 2p anyway Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Zathras]

Thanks, Geoff. I did not know all of those details. Your post is much appreciated.

 

[krinid]

I'm familiar with Workbook_ events, but not sure about these backward compatibility ones. Could you put up some sample code to show their usage? I looked in the help files and found

Workbooks.Open "ANALYSIS.XLS"
ActiveWorkbook.RunAutoMacros xlAutoOpen

and

With ActiveWorkbook
    .RunAutoMacros xlAutoClose
    .Close
End With

but I'm afraid this isn't aiding my understanding.

 

[Stretchwickster]

I basically learned VBA from a colleauge's VBA project and I think I picked up some bad habits, one of which was using this seemingly undocumented Autpen technique. Here's an example of what I would normally put in a module called Mod_AutoRun plus some random code:

Sub Auto_Open()
  
  Dim startCell As Range
  Dim endCell As Range
  
  Worksheets(1).Activate
  Set startCell = Range("A1")
  Set endCell = Cells(4, Windows(1).VisibleRange.Columns.Count)
  With Range(startCell, endCell)
    .Merge
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Value = "My Main Title"
    .Font.Size = 20
    .Font.Color = RGB(255, 255, 255)
  End With
  Range("A10").Activate
  Application.WindowState = xlMaximized
  Application.ScreenUpdating = False
  Mod_Global.Str_ERPwbName = ThisWorkbook.Name
  Mod_Global.Sgl_LowerThreshold = Mod_Global.SGL_LOWER_THRESHOLD
  Mod_Global.Sgl_UpperThreshold = Mod_Global.SGL_UPPER_THRESHOLD
  Mod_Global.Bool_DataAndResultFilesLoaded = False
  Mod_Global.Bool_FileRequired = True
  Call MyProcessingFunction
  Application.ScreenUpdating = True

End Sub

Sub Auto_Close()

  Mod_CommandBar.DeleteCommandBar(Mod_Global.STR_COMMAND_BAR_NAME) 'Delete toolbar
  If Not (Mod_Global.Wksh_DataFile Is Nothing) Then _
   Set Mod_Global.Wksh_DataFile = Nothing
  If Not (Mod_Global.Wksh_ResultFile Is Nothing) Then _
    Set Mod_Global.Wksh_ResultFile = Nothing
  
End Sub

When you run the workbook it automatically runs the code found in Autpen - I presumed that this was the normal way to do it until I was informed of the Workbook events.

Hope this helps!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[krinid]

So you put that code in a module (as opposed to the workbook code page) and it automatically runs when the workbook is opened? Does the name of the module play a role at all?

What would happen if there were multiple Autpen subs in different modules?

 

[Stretchwickster]

>So you put that code in a module (as opposed to the workbook code page) and it automatically runs when the workbook is opened?
Yes.

>Does the name of the module play a role at all?

No.

>What would happen if there were multiple Autpen subs in different modules?
As the workbook opens you get an error message saying "Ambiguous name detected: Autpen" and you can proceed no further.

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[krinid]

Thanks for the info!