Only run one time when worksheet open

Question

Only run one time when worksheet open

Anonymous

Hi

I would like to ask that that I have a code on worksheet open()

which I would like to run one time only

means once the file is open first time it will execute the code

after closing the file and then the same file will open then it wont execute the code after user form which is given below

In detail

Private Sub Workbook_Open()

Dim ws As Worksheet

Dim pwd As String

ViewOnly.Show

pwd = "password"

Worksheets("Confidential").Visible = False

For Each ws In ActiveWorkbook.Worksheets

If ws.Name <> "Confidential" Then

ws.Unprotect Password:=pwd

ws.Range("C3:D3").Locked = False

ws.Range("D5").Font.Color = vbWhite

ws.Protect Password:=pwd

End If

Next ws

ActiveSheet.Range("C3:D3").Select

End Sub

ViewOnly userform has two buttons

which has the following code

Private Sub cmdClose_Click()

ActiveWorkbook.Close

End Sub

Private Sub cmdOk_Click()

Unload Me

End Sub

Thanks

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

OssieMac 48,006

Just add a worksheet and make it VeryHidden and test for this worksheet and if found then exit the sub.

A VeryHidden worksheet cannot be Unhidden from the interactive mode and in fact the user will not be aware of its existence. Need to UnHide with VBA code using Worksheets("TestSheet").Visible = xlSheetVisible

Do you realize that with a Modal Userform that the code after the Show command does not run until after the Userform is closed? With a Modeless Userform then the code after the Show command continues to run immediately after showing the userform.

Private Sub Workbook_Open()

Dim ws As Worksheet

Dim wsActive As Worksheet

Dim wsTest As Worksheet

Dim pwd As String

ViewOnly.Show

'With a Modal Userform the following code does not run until Userform closes

Set wsActive = ActiveSheet 'Need to return to active sheet after adding worksheet

On Error Resume Next

Set wsTest = Worksheets("TestSheet")

On Error GoTo 0

If Not wsTest Is Nothing Then 'Not nothing then is something so sheet exists

MsgBox "Code has run once so exit here." 'Delete this line after testing

Exit Sub

End If

pwd = "password"

Worksheets("Confidential").Visible = False

'Worksheets("Confidential").Visible = xlSheetVeryHidden 'Optional in lieu of previous line

For Each ws In ActiveWorkbook.Worksheets

If ws.Name <> "Confidential" Then

ws.Unprotect Password:=pwd

ws.Range("C3:D3").Locked = False

ws.Range("D5").Font.Color = vbWhite

ws.Protect Password:=pwd

End If

Next ws

Application.ScreenUpdating = False

Sheets.Add After:=Sheets(Sheets.Count)

Set wsTest = ActiveSheet

wsTest.Name = "TestSheet" 'Can use any name but edit test code above to same name

wsActive.Activate 'Return to active sheet before hiding added sheet

wsTest.Visible = xlSheetVeryHidden 'Very Hidden cannot be unhidden from Interactive mode

ActiveSheet.Range("C3:D3").Select

End Sub

You could perform a similar thing by inserting a value (True or False or any value you prefer) in an out of the way cell in one of your existing worksheets and test for the existence of that value.

0 comments

1 additional answer

Answer 1

Anonymous

Hi

OssieMac

You are great, excellent job, awesome work, amazing concept, marvelous code.

Keep me in good book

Thanks

0 comments

Answer 2

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more