A way to make the Security Warning more visible?

[hazmat]

I know this is a long shot, but is there any way to have the security warning that shows when someone else first opens my Macro-enabled workbook, show up in the middle of the page?
Or even a message that pops up that tells the user that they have to enable content for the worksheet to work properly?

I'm trying to make my workbook as dumb-dumb proof as possible. The small Security Warning that shows at the top of the program can easily be missed.

I was thinking i could maybe add a WordArt Shape over the whole sheet, telling them the have to click the 'enable content' button, then with my first line of code, remove the shape, but was hoping there is a better solution.

thanks

 

[Michael M]

I think you'll find your last comment

I was thinking i could maybe add a WordArt Shape over the whole sheet, telling them the have to click the 'enable content' button, then with my first line of code, remove the shape.

Is exactly what you need....put it in the Workbook_open event
and make the readOnly = False at the same time

 

[hazmat]

thanks, ill play around with that then.
Not sure about the ReadOnly part though. I'm just a dumb-dumb, trying to make a spreadsheet for other dumb-dumbs, lol

 

[Michael M]

Yeah on further thought.....You can't enableContent via VBA....it would defeat rhe purpose of the security !!.....dumb-dumb at this end too !!
With regard to the Message to the user.
1. create new sheet, call it say "Content"
2. Create a LARGE visual text box on that sheet with a LARGE button
3. Have the workbook_open event open on that sheet and when the button is clicked the "Content" worksheet will be hidden
4. You will also need a Workbook Before close event to re Unhide the sheet for next time

Keep in mind though if someone disables macros.....none of this will work.

 

[hazmat]

Pretty easy once i did some Googling. I have a problem with figuring out the proper syntax with the code.
I originally had the Sheets.("Start").Visible = False at the top of the Workbook_Open code, but it turned out that it was trying to hide the only visible sheet
before unhiding the others. It didn't like that.
I put it at the bottom and all is well.

Now I just have to figure out how to make sure that it always opens on the HazMat OT sheet.

Thanks again for your help

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Sheets("Start").Visible = True
    Sheets("HazMat OT").Visible = False
    Sheets("Instructions").Visible = False
    Sheets("Notes").Visible = False
    Sheets("Reference").Visible = False
    ActiveWorkbook.Save

End Sub

Private Sub Workbook_Open()
    'scrolls to top of page on workbook open
    ActiveWindow.ScrollIntoView 1, 1, 1, 1
    
    Sheets("HazMat OT").Visible = True
    Sheets("Instructions").Visible = True
    Sheets("Notes").Visible = True
    Sheets("Reference").Visible = True
    Sheets("Start").Visible = False
End Sub

 

[Michael M]

How about this way

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name <> ("HazMat OT") Then
        ws.Visible = False
    End If
Next ws
Sheets("HazMat OT").Range("A1").Activate
End Sub

 

[hazmat]

That works if i remove .Range("A1"), otherwise I get an error. Don't know why.
I just did it like this

Sheets("HazMat OT").Activate
ActiveWindow.ScrollIntoView 1, 1, 1, 1

On an other note, what is the advantage of using

Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name <> ("HazMat OT") Then
        ws.Visible = False
    End If
Next ws

as opposed to

Sheets("HazMat OT").Visible = True
    Sheets("Instructions").Visible = False
    Sheets("Notes").Visible = False
    Sheets("Reference").Visible = False
    Sheets("Start").Visible = False

 

[Michael M]

Just more efficient...

 

[hazmat]

Gotcha, Thanks
Maybe I'll start doing things that way. I kind of understand what it does, but don't fully understand what it all means.
Listing each instruction is just more intuitive to me, for now.

 

[Michael M]

So

Dim ws As Worksheet ' declare your variable
For Each ws In Worksheets 'loop through all worksheets
    If ws.Name <> ("HazMat OT") Then ' if the sheets name ISN'T "Hazmat" 
        ws.Visible = False 'hide it
    End If
Next ws 'go to next worksheet and repeat process