VBA code writing help

[max8719]

Hi all,


I have 2 codes which I want to use in my workbook. But I don't know how to combine them in to one as I am a complete beginner but have read and now under the impression you can not have to events that are the same.

Code 1
Private Sub Workbook_BeforeClose(Cancel As Boolean)
bIsClosing = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wsArray() As Variant
Dim iCnt As Integer
Application.ScreenUpdating = 0

Splash.Visible = True

For Each wsSht In ThisWorkbook.Worksheets
If Not wsSht.CodeName = "Splash" Then
If wsSht.Visible = True Then
iCnt = iCnt + 1: ReDim Preserve wsArray(1 To iCnt)
wsArray(iCnt) = wsSht.Name
End If
wsSht.Visible = xlSheetVeryHidden
End If
Next

Application.EnableEvents = 0
ThisWorkbook.Save
Application.EnableEvents = 1

If Not bIsClosing Then
For iCnt = 1 To UBound(wsArray)
Worksheets(wsArray(iCnt)).Visible = True
Next iCnt
Splash.Visible = False
Cancel = True
End If

Application.ScreenUpdating = 1
End Sub
Private Sub Workbook_Open()
Dim wsSht As Worksheet


For Each wsSht In ThisWorkbook.Worksheets
wsSht.Visible = xlSheetVisible
Next wsSht

Splash.Visible = xlSheetVeryHidden

bIsClosing = False
End Sub

Code 2



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
If Sh.Name = HomeSheet Then
'do nothing
Else
Sh.Visible = xlSheetVeryHidden
Sh.Protect Password:=shPassword
End If
Next Sh
End Sub


Private Sub Workbook_Open()


ShowSheets wb:=ThisWorkbook
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "User List" Then BuildTable ws:=Sh
End Sub

<strike></strike>

Thanks in advance

 

[Norie]

Doesn't the code in the BeforeSave event do something very similar to the code in the BeforeClose event of the second set of code, ie hide/show certain sheets.

 

[max8719]

The sheets I would like showing are in code 1. What I am trying to achieve is force the user to enable content or macros when opening the document. I found something that would show a message when opening that the user needs to enable them and if they don't accept the document closes. I am a bit baffled to be fair.

 

[Norie]

Which parts of the posted code do what you want and when do you want them to be executed?

The usual method to do what you describe is to have a 'splash' worksheet that displays a message about enabling macros.

That sheet would be the only one visible when the workbook opened.

 

[svendiamond]

Max, are you trying to skip the yellow button that pops up that says "Enable Content" or the message that makes you choose "Allow Macros"?

 

[max8719]

I am indeed trying to get around the enable button, however I have come across another problem . When I want to share the work book its popping up with runtime errors regarding protection and unprotection of sheets.