CODEWITHSUNDEEP
vbaexcelexcel-2010
Jord27
Jord27

Reputation: 5

Userform to open on only specific sheets

I am trying to create a workbook which is for parcel data, the sheets on the work book are specific to the weeks in the year so i have 52 sheets for the weeks named WK 1, Wk 2, Wk 3, etc etc.. and another sheet called "Front" which is displayed automatically when the user opens the workbook even if it saved on another sheet.

From the front screen they can select the current week number thus leading them too the sheet and the userform pops up for the data entry to begin, my only problem i have at the minute is if i click back onto the front page the user form appears. Is there any way that i can disable it from opening?

I have currently got some code for the "Font" sheet which stops it from opening when the work book is opened but it doesnt stop it when i move to a sheet and back again.

So this is currently what i have for my front sheet code.

Private Sub ComboBox1_Change()
Sheets(ComboBox1.Value).Select
Application.EnableEvents = False
Application.EnableEvents = True

And this is currently what i have for the workbook code.

Private Sub Workbook_Open()
Worksheets("Front").Activate

Dim iCount As Integer

Sheet1.ComboBox1.Clear

For iCount = 1 To Sheets.Count
    Sheet1.ComboBox1.AddItem Sheets(iCount).Name
Next iCount
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Call OpenDataEntryForm
End Sub

Public Sub OpenDataEntryForm()

Dim dataEntryForm As ParcelDataEntry

Set dataEntryForm = New ParcelDataEntry

dataEntryForm.Show

Set dataEntryForm = Nothing

End Sub

Upvotes: 0

Views: 1433

Answers (1)

Tim Williams
Tim Williams

Reputation: 166156

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name <> "Front" Then OpenDataEntryForm
End Sub

Upvotes: 1

Related Questions