Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 542
  • Last Modified:

Users need to enable macros to view the workbook

I need to have users enable macros to view my workbook. If a user does not enable macros my workbook needs to either close or hide all the sheets but the warning sheets that tells them MACROS NEED TO BE ENABLED.
I've read other comments people on expertexchange have posted on this topic, but for some reason I cannot seem to be able to properly implement it into my workbook. I need to use either autostart macro to do this or Workbook_Open, and I need the following code to somehow be implemented within:

Sub Auto_Open()
Application.ShowWindowsInTaskbar = True
Application.WindowState = xlMinimized
Application.Assistant.Visible = False
frmMain.Show
End Sub

This is what needs to execute if macros are enabled. If macros are disabled then we need something that will hide all the other sheets, but the warning sheet.

Thanks
MJ
0
bnemeth
Asked:
bnemeth
  • 7
  • 3
  • 2
1 Solution
 
Dave BrettCommented:
Hi MJ,

If macros are disabled then you can't run code to hide the sheets. The solution is

1) Add code that makes all the sheets except for a splashscreen hidden when the file is saved
2) If macros are enabled when the file is opened then use code to unhide all the sheets. If macros are disabled then the splashscreen saying "You must enable macros" is displayed


So create a menu sheet called "Splash screen". To hide and unhide the worksheets (depending on whether macros are enabled), paste the following code into the ThisWorkbook code sheet:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet, wsSplash As Worksheet
Application.ScreenUpdating = False
Application.EnableEvents = False
Set wsSplash = Worksheets("Splash screen")
wsSplash.Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVeryHidden
Next ws
Cancel = True
ThisWorkbook.Save
For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVisible
Next ws
wsSplash.Visible = xlSheetVeryHidden
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Open()
Dim ws As Worksheet, wsSplash As Worksheet
Application.ScreenUpdating = False
Set wsSplash = Worksheets("Splash screen")
wsSplash.Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVisible
Next ws
wsSplash.Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
Application.ShowWindowsInTaskbar = True
Application.WindowState = xlMinimized
Application.Assistant.Visible = False
frmMain.Show
End Sub

Cheers

Dave
0
 
bnemethAuthor Commented:
I can't have users be able to save the file, I tried using the above code, but it didn't work for me. I've seen other similar solutions that other users posted, but for some reason it doesn't work for my workbook. Probably because I have many other variables affecting the code. This is exactly what I need. I already have an auto macro, which has auto_open & auto_close.

Sub Auto_Open()
Application.ShowWindowsInTaskbar = True
Application.WindowState = xlMinimized
Application.Assistant.Visible = False
frmMain.Show
End Sub

Sub Auto_Close()
    Sheets("Menu").Select
    Application.DisplayFullScreen = False
    Workbooks("Eastern Region Physician Report 2004.xls").Close SaveChanges:=False
End Sub

I don;t want to enable the save at the time of close, and also the frmMain OK button has the following code:

Private Sub cmdOK_Click()
If txtPassword.Value = Worksheets("Password").Range("B3").Value Then
Unload frmMain
Dim wb As Workbook
    Set wb = Workbooks("Eastern Region Physician Report 2004.xls")
    Windows(wb.Name).Visible = True
'Disables ToolBars
    Application.CommandBars("Worksheet Menu Bar").Enabled = False
    Application.CommandBars("Standard").Enabled = False
    Application.CommandBars("Formatting").Enabled = False
    Application.CommandBars("Borders").Enabled = False
    Application.CommandBars("Chart").Enabled = False
    Application.CommandBars("Control Toolbox").Enabled = False
    Application.CommandBars("Drawing").Enabled = False
    Application.CommandBars("External Data").Enabled = False
    Application.CommandBars("Forms").Enabled = False
    Application.CommandBars("Formula Auditing").Enabled = False
    Application.CommandBars("Picture").Enabled = False
    Application.CommandBars("Pivottable").Enabled = False
    Application.CommandBars("Protection").Enabled = False
    Application.CommandBars("Reviewing").Enabled = False
    Application.CommandBars("Text to speech").Enabled = False
    Application.CommandBars("Visual Basic").Enabled = False
    Application.CommandBars("Watch Window").Enabled = False
    Application.CommandBars("Web").Enabled = False
    Application.CommandBars("WordArt").Enabled = False
    Application.CommandBars("3-d settings").Enabled = False
    Application.CommandBars("Chart Menu Bar").Enabled = False
    Application.CommandBars("Circular Reference").Enabled = False
    Application.CommandBars("Diagram").Enabled = False
    Application.CommandBars("Drawing Canvas").Enabled = False
    Application.CommandBars("Exit Design Mode").Enabled = False
    Application.CommandBars("Full Screen").Enabled = False
    Application.CommandBars("Organization Chart").Enabled = False
    Application.CommandBars("Shadow Settings").Enabled = False
    Application.CommandBars("Stop Recording").Enabled = False
    Application.DisplayFullScreen = True
    Sheets("Menu").Select
'Hides Certain Sheets
    Sheets("Password").Visible = False
    Sheets("PrimaryPivot").Visible = False
    Sheets("lists").Visible = False
    Sheets("chartpivotprimary").Visible = True
    Sheets("ChartIndividual").Visible = True
    Worksheets("SourceDataIndividual").Visible = xlSheetVeryHidden
    Sheets("GroupList").Visible = False
    Sheets("ChartPivotPrimary").Visible = False
    Sheets("ChartPivotPrimarycontribution").Visible = False
    Sheets("Chartpivotprimaryindividual").Visible = True
'Resets Pivot Tables
    Worksheets("ChartPivotPrimary").PivotTables("PivotTable1").PivotFields("FAC_DESC").CurrentPage = _
        "(All)"
    Worksheets("ChartPivotPrimary").PivotTables("PivotTable1").PivotFields("INOUT_CODE").CurrentPage = _
        "(All)"
    Worksheets("ChartPivotPrimary").PivotTables("PivotTable1").PivotFields("ATTEND_SPEC").CurrentPage = _
        "(All)"
    Worksheets("ChartPivotPrimaryIndividual").PivotTables("PivotTable1").PivotFields("FAC_DESC").CurrentPage = _
        "(All)"
    Worksheets("ChartPivotPrimary").PivotTables("PivotTable1").PivotFields("INOUT_CODE").CurrentPage = _
        "(All)"
    Worksheets("ChartPivotPrimary").PivotTables("PivotTable1").PivotFields("ATTEND_SPEC").CurrentPage = _
        "(All)"
    Worksheets("ChartPivotPrimaryIndividual").PivotTables("PivotTable1").PivotFields("ATTEND_SPEC").CurrentPage = _
        "(All)"
    Worksheets("ChartPivotPrimaryIndividual").PivotTables("PivotTable1").PivotFields("ATTEND_PHYS_NAME").CurrentPage = _
        "(All)"
    Worksheets("PivotChartLink").PivotTables("PivotTable2").PivotFields("HOSP").CurrentPage = _
        "(All)"
    Worksheets("PivotChartLink").PivotTables("PivotTable2").PivotFields("IN / OUT").CurrentPage = _
        "(All)"
    Worksheets("PivotChartLink").PivotTables("PivotTable2").PivotFields("SPECIALTY").CurrentPage = _
        "(All)"
    Worksheets("PivotChartLink").PivotTables("PivotTable2").PivotFields("PHYS. GROUP").CurrentPage = _
        "(All)"
    Worksheets("PivotChartLink").PivotTables("PivotTable2").PivotFields("PHYS. GROUP ROLLUP").CurrentPage = _
        "(All)"
    Else
    Dim intWrongPassword As Integer
    intWrongPassword = MsgBox("Incorrect Password! - Please Contact Medical Operations.", vbExclamation + vbRetryCancel, "Authorized Access Only")
    If intWrongPassword = vbRetry Then
    frmMain.Show
    Else
    Workbooks("Eastern Region Physician Report 2004.xls").Close SaveChanges:=False
    Unload frmMain
    End If
    End If
End Sub

Hope this is not too confusing!!

Thanks,

MJ

0
 
bnemethAuthor Commented:
The above set of codes work great for me, but only if macros are enabled. If not then my workbook opens without the password, and users are able to see all of the confidential information.

Besides this problems I have another issue. My application starts in fullscreen mode, and I have command buttons on each sheet helping users navigate throughout the workbook easily. When usres navigate in full screen mode via command buttons, the top part of the excel (where normally toolbars stand), blacks out, and we can't see maybe three inches of the screen. Anybody know why.

I know my matter is getting more confusing, so I'm rasing the total points value,

Thanks

MJ
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Dave BrettCommented:
MJ,

> The above set of codes work great for me, but only if macros are enabled.

You will have to use code such as my code above - there isn't any other way around it. You must force users to enable macros

> I can't have users be able to save the file, I tried using the above code, but it didn't work for me

Its possible that events are turned off

Try running this sub

Sub RestoreEvents()
Application.EnableEvents = True
End Sub

Does the Before Save event fire now?

You should run the command off the save rather than close, otherwise a user can close the file and it won't be opened correctly the next time

Cheers

Dave
0
 
bnemethAuthor Commented:
Application.enableEvents = True worked, thanks for that?

However I still have two problems:
01. I have two charts in my workbook, and when macros DISABLED by the user it does not hide charts (it only hides sheets)

02. User prompted to save changes at closing, can't have this???

How do we stop excel from asking if they want to save changes???


Thanks MJ

0
 
cloaked_identityCommented:
Hi MJ,

> I have two charts in my workbook, and when macros DISABLED by the user it does not hide charts (it only hides sheets)

You can copy the charts to a worksheet if that helps.

> User prompted to save changes at closing, can't have this???

Try setting the workbook as read-only. It will prevent them from saving over your file.

Hope this helps,
Andrew
0
 
bnemethAuthor Commented:
My charts are pivot table charts, controled by whatever user chooses in the pivot table, they have to be on a seperate sheet.

Setting workbook as read only did not help, still prompting to save a copy of the file.

????
0
 
cloaked_identityCommented:
MJ,

Try protecting your worksheets at     Tools > Protection > Protect Sheet     to prevent users from editing the worksheets thus there will be no changes. Also protect workbook at      Tools > Protection > Protect > Protect Workbook     to prevent users from editing the layout (ie. sheet order etc.). This might solve the save prompt problem but i am still thinking of a solution for you chart problem.

Hope this helps,
Andrew
0
 
bnemethAuthor Commented:
I can't protect my workbook that way, it blocks users from using my pivot tables. I have protected it using a form (text box) and if statement. That works well for me.

I guess the reason I have to do it the hard way, is because of my pivot tables.


Thanks for your help.

MJ
0
 
bnemethAuthor Commented:
I Used the following code to stop excel from asking if I want to save the workbook:

Workbooks("Workbook Name").close savechanges:=false
0
 
Dave BrettCommented:
Hi MJ

< Workbooks("Workbook Name").close savechanges:=false

Thats right

either use
Workbooks("Workbook Name").Close False
or
Workbooks("Workbook Name").Saved = True
whcih fools Excel into thinking there are no changes to be saved

Cheers

Dave
0
 
bnemethAuthor Commented:
Thanks for your help!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now