CODEWITHSUNDEEP
vbaexcel
Matt
Matt

Reputation: 123

How can I run a macro as a workbook opens for the first time only?

I've got a workbook which runs a macro to show the userform Open1 as it opens, using the (very basic) code:

Private Sub Workbook_Open()

    Open1.Show

End Sub

This does its job fine - each time I open the workbook, the userform pops up and runs perfectly.

But, I want the userform to appear the first time the workbook is opened only. Is there a way to allow this to happen?

Upvotes: 2

Views: 2467

Answers (4)

Tragamor
Tragamor

Reputation: 3634

Based on the idea supplied by PaulG, I have coded an upgrade that will check for the name and if not found run a function, add the name and save the workbook for a more seemless approach to this problem...

Placed in ThisWorkbook

Private Sub Workbook_Open()
    Run "RunOnce"
End Sub

Placed in a module

Sub RunOnce()
    Dim Flag As Boolean: Flag = False
    For Each Item In Application.Names
        If Item.Name = "FunctionHasRun" Then Flag = True
    Next
    If Flag = False Then
        Call Application.Names.Add("FunctionHasRun", True, False)
        Application.DisplayAlerts = False
            ActiveWorkbook.Save
        Application.DisplayAlerts = True
        Call RunOnceFunction
    End If
End Sub

Private Function RunOnceFunction()
    Open1.Show
End Function

Sub ResetRunOnce()
    For Each Item In Application.Names
        If Item.Name = "FunctionHasRun" Then
            Application.Names.Item("FunctionHasRun").Delete
            Application.DisplayAlerts = False
                ActiveWorkbook.Save
            Application.DisplayAlerts = True
        End If
    Next
End Sub

Upvotes: 0

Tragamor
Tragamor

Reputation: 3634

You could use a dummy module which gets deleted the first time you open the spreadsheet...

Something like:

If ModuleExists("DummyModule") Then
    Open1.Show
    DoCmd.DeleteObject acModule, "DummyModule"
End If

Function ModuleExists(strModuleName As String) As Boolean
    Dim mdl As Object
    For Each mdl In CurrentProject.AllModules
        If mdl.Name = strModuleName Then
            ModuleExists = True
            Exit For
        End If
    Next
End Function

Update: as stated, DoCmd isn't used in excel vba. That will teach me to write code without testing it! The following updated code will work, but in order to access the VB environment, excel needs to be trusted.

There is a setting in the Trust Center>Macro Settings that you can tick for this code to work under Developer Macro Settings

As such, this may not be the way to go as it opens up the possibility of security issues...

Sub RemoveModule()
    If ModuleExists("DummyModule") Then
        Open1.Show
        Dim vbCom As Object: Set vbCom = Application.VBE.ActiveVBProject.VBComponents
        vbCom.Remove VBComponent:=vbCom.Item("DummyModule")
    End If
End Sub

Function ModuleExists(strModuleName As String) As Boolean
    Dim mdl As Object
    For Each mdl In Application.VBE.ActiveVBProject.VBComponents
        If mdl.Name = strModuleName Then
            ModuleExists = True
            Exit For
        End If
    Next
End Function

Upvotes: 2

PaulG
PaulG

Reputation: 1199

Here's an alternative bit of code that will persist between saves and allow you to reset it. No need to create a hidden sheet. Put this in a module (invoke the DisplayFormIfFirstTime from your Workbook_Open event handler....)

Option Explicit

Private Const cMoniker As String = "FormHasBeenDisplayed"

Private Sub DisplayFormIfFirstTime()
If HasBeenOpened = False Then DisplayForm
End Sub

Public Sub DisplayForm()
MsgBox "Ok, its not a form but a dialog box...", vbInformation
End Sub

Public Function HasBeenOpened() As Boolean

Dim oName As Name
On Error Resume Next
Set oName = Application.Names(cMoniker)
On Error GoTo 0

If Not oName Is Nothing Then
    HasBeenOpened = True
Else
    Call Application.Names.Add(cMoniker, True, False)
End If

End Function

'Call this to remove the flag...
Public Sub ResetOpenOnce()
On Error Resume Next
Application.Names(cMoniker).Delete
End Sub

Upvotes: 0

WltrRpo
WltrRpo

Reputation: 263

Try this:

If Sheets("Hide").Cells(1,1) = "1" Then
    Open1.Show
    Sheets("Hide").Cells(1,1) = "0"
End if

You must create the sheet Hide, and give the cell A1 the value 1, in that case the form will be shown.

After you create the sheet, hide it with this

Sheets("Hide").Visible = xlVeryHidden

And show it with this

Sheets("Hide").Visible = True

Upvotes: 0

Related Questions