A way to make the Security Warning more visible?

hazmat

New Member
Joined
Jun 14, 2019
Messages
19
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,523
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I think you'll find your last comment
Rich (BB code):
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

New Member
Joined
Jun 14, 2019
Messages
19
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,523
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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.:cool:
 

hazmat

New Member
Joined
Jun 14, 2019
Messages
19

ADVERTISEMENT

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

VBA Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,523
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
How about this way
VBA Code:
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

New Member
Joined
Jun 14, 2019
Messages
19

ADVERTISEMENT

That works if i remove .Range("A1"), otherwise I get an error. Don't know why.
I just did it like this
VBA Code:
Sheets("HazMat OT").Activate
ActiveWindow.ScrollIntoView 1, 1, 1, 1

On an other note, what is the advantage of using
VBA Code:
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
VBA Code:
Sheets("HazMat OT").Visible = True
    Sheets("Instructions").Visible = False
    Sheets("Notes").Visible = False
    Sheets("Reference").Visible = False
    Sheets("Start").Visible = False
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,523
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Just more efficient... :cool:
 

hazmat

New Member
Joined
Jun 14, 2019
Messages
19
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,523
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
So
Rich (BB code):
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
 

Forum statistics

Threads
1,136,303
Messages
5,674,971
Members
419,537
Latest member
ucatchy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top