Excel VBA: Enabling Macro Settings

[judithpack]

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?

[arnelgp]

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.

[judithpack]

Thanks for your answer. I got it.

[Jan Karel Pieterse]

You could do something like in the attached.

[Logit]

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.

'  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

[judithpack]

Thanks for your answer. I got it.

[Logit]

Glad to help.