Printable View
Hello,
I wrote a macro method to auto-size the columns in an Excel sheet upon opening the sheet. However, this solution does not work in the case that the user has not enabled macros in Excel.
Is there any way to check if the user has VBA macros disabled in Excel, and then enable the macros with Excel VBA code?
any VBA won't run anyway if the Macro is not enabled in Excel.
therefore, the only way to do it is through Trust Center setting.
maybe add a Label on a cell reminding users to Enable the macro first.
Thanks for your answer. I got it.
You could do something like in the attached.
Here is a method to check if the user has macros enabled. If the answer is "no", they will be presented with a worksheet (normally hidden) that advises macros need to be enabled
and given basic instructions how to do so. If macros ARE ENABLED to begin with, the workbook opens normally and the "non-macro worksheet" is not shown.
Code:' When you have completed creating your final workbook project, uncomment those lines of code below' that are presently commented out. BE CERTAIN YOU UNDERSTAND ONCE THESE CODES LINES ARE MADE ACTIVE
' IF USER HAS MACROS ENABLED ON THEIR MACHINE, THE SHEET "MACROS AND SETUP" WILL BE DELETED AND
' SHEETS 2 & 3 WILL BE CHANGED TO VISIBLE.
' ###############################################################################
Sub MsgYesNoSub()
Dim Ans As Integer
Ans = MsgBox("Macros are enabled." & vbNewLine & vbNewLine & _
"Continue workbook setup ? Press Yes/No", vbYesNo + vbDefaultButton1, "Continue Setup Yes/No")
If Ans = vbYes Then
WBSetup
Else
MsgBox "You chose No."
Application.Visible = False
Workbooks("Macros Disabled.xlsb").Close savechanges:=False
Application.Visible = True
End If
End Sub
Sub WBSetup()
Application.DisplayAlerts = False
'Uncomment next line for final workbook.
'Sheets("Macros and Setup").Delete
Application.DisplayAlerts = True
Picture1_Click
Sheets("Sheet1").Visible = xlSheetVisible
Sheets("Sheet2").Visible = xlSheetVisible
Sheets("Sheet3").Visible = xlSheetVisible
Sheets("Macros and Setup").Visible = xlSheetVeryHidden
Sheets("Sheet1").Activate
Sheets("Sheet1").Range("A1").Select
MsgBox "Setup Complete !"
End Sub
Sub Pause(Optional P! = 0.01)
Dim D!, F!
D = Timer
F = D + P
While Timer < F
If Timer < D Then F = F - 86400: D = 0
DoEvents
Wend
End Sub
Sub Picture1_Click()
Const R = 50000
Dim S As Boolean, N As Byte, Sh As Shape, P As Byte
Dim L As Long
Dim ws As Worksheet
Dim img As Shape
Dim centerX As Double, centerY As Double
Dim imgWidth As Double, imgHeight As Double
' Set the worksheet
Set ws = Sheets("Sheet1")
' Save the workbook's saved state
S = ThisWorkbook.Saved
' Hide all shapes in the worksheet
For Each Sh In ws.Shapes
Sh.Visible = msoFalse
Next
' Show progress
Progress.Show vbModeless
Pause 0.6 ' only for this demo
' Progress bar logic
N = 1
For L = 1 To R
P = L * 100 \ R
If P >= N Then
Progress.Bar.Width = Progress.Border.Width * L / R
Progress.Text.Caption = P & "% COMPLETE"
Pause ' only for this demo, DoEvents only in real workbook
N = N + 1
End If
Next
Pause 0.3 ' only for this demo
Unload Progress
' Position the image in the center of the worksheet
For Each Sh In ws.Shapes
If Sh.Type = msoPicture Then
' Get worksheet dimensions
centerX = ws.Columns(1).Width * ws.UsedRange.Columns.Count / 2
centerY = ws.Rows(1).Height * ws.UsedRange.Rows.Count / 2
' Get image dimensions
imgWidth = Sh.Width
imgHeight = Sh.Height
' Calculate position for centering
Sh.Left = centerX - imgWidth / 2
Sh.Top = centerY - imgHeight / 2
' Make the image visible
Sh.Visible = msoTrue
Else
Sh.Visible = msoTrue
End If
Next
' Restore workbook's saved state
ThisWorkbook.Saved = S
End Sub
Thanks for your answer. I got it.
Glad to help.