CODEWITHSUNDEEP
excelvbalockingfreezeediting
steventnorris
steventnorris

Reputation: 5896

Freeze excel editing while vba is running

I have an excel sheet that I want to edit via vba script, but I need the users to not be able to edit any cells or click any buttons while the script is running. Due to a programs that I Shell after the excel button click, editing is enabled during this VBA sub run. What I need is a way to programmaticaly lock all cells for editing except by VBA. How can I do this?

Psuedo_Code:

Shell _ "PROGRAM1"
Shell _ "PROGRAM2"
Dim shellobject As PROGRAM2
shellobject.Connect "PROGRAM1"
shellobject.dothings
if(shellobject.success) then
Cells(1,1).Value = "HUZZAH!"
Else
Cells(1,1).Value = "OH NO MR BILL!"
End If
shellobject.dootherthings

etc.....

Upvotes: 0

Views: 11035

Answers (4)

Manoj
Manoj

Reputation: 31

Following worked for me:

Use Application.Interactive = False at the beginning and re-enable it at the end. This will disable user clicks/entry attempts until the Macro/Userform/any code part completes its operation.

Upvotes: 3

Tariq Khalaf
Tariq Khalaf

Reputation: 89

I was trying to comment to Steventnorris and Even-Steven but I am not able to, so just putting an answer here. 

Application.DataEntryMode= xlOff in the beginning should be set to
Application.DataEntryMode= xlOn 
and then at the end of the code
put in  
Application.DataEntryMode= xlOff

I just tried this and it has stopped me from editing the sheet so in case anyone else is trying.

Upvotes: 0

Peter Albert
Peter Albert

Reputation: 17485

As you are using a UserForm, set it to be modal in the Properties dialog of the form (press F4 in case it is not visible):

enter image description here

This way, the user cannot click in Excel outside the form unless it is closed.

Also, you can try to disable the buttons during the execution. This code should do that:

Private Sub CommandButton1_Click()
    SetButtonsEnabled False
    Application.EnableEvents = False

    'Your code here

    SetButtonsEnabled
    Application.EnableEvents = True

End Sub

Private Sub SetButtonsEnabled(Optional blnEnable As Boolean = True)
    Dim btn As Control
    For Each btn In Me.Controls
        If TypeOf btn Is CommandButton Then btn.Enabled = blnEnable
    Next
End Sub

Upvotes: 3

even-steven
even-steven

Reputation: 88

Have you considering disabling input from the keyboard. This could be a brute force way of accomplishing what you need but it's a thought.

You can use 

Application.DataEntryMode =

to disable input from the keyboard. Set it to off once your script starts and turn it back on right before your script ends.

Again, this could be considered brute force but just an idea.

Hope that helps.

Upvotes: 4

Related Questions