CODEWITHSUNDEEP
excelvba
MSauce
MSauce

Reputation: 123

Override MsgBox in Excel

I have a form that requires a user to 'Sign' an excel file before saving. Issue is, I can't save the worksheet if that cell is blank, and I don't want to fill out the form with 'fake data' in order to save the file, as this will prevent the user from being alerted in signing the form.

Ideally I would like a msgbox that has some sort of password input from myself that would bypass this validation that would allow me to save the file when cell C19 is blank when I enter the correct password. 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As 
Boolean)

If Application.Sheets("Sheet1").Range("C19").Value = "(INSERT NAME HERE)" Or Application.Sheets("Sheet1").Range("C19").Value = "" Then
cancel = True
MsgBox "Please input your name in cell C19"
End If

End Sub

Upvotes: 0

Views: 102

Answers (1)

Mikku
Mikku

Reputation: 6654

Will something like this work ?

If you Enter MSauce it will save without name, or if you enter any other name it will save and enter that value in C19, else it won't save

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)

Dim str As String

str = Application.Sheets("Sheet1").Range("C19").Value

If str = "(INSERT NAME HERE)" Or str = "" Then

    str = InputBox("Please input your name", "Missing Name")

        If str = "MSauce" Then
            Exit Sub
        ElseIf Len(str) > 0 Then
            Application.Sheets("Sheet1").Range("C19").Value = str
        Else: cancel = True
        End If


End If

End Sub

Upvotes: 1

Related Questions