PDA

View Full Version : Solved: YesNo Box - Options Query



Phelony
05-28-2009, 07:18 AM
Hey Guys :hi:

I've written the below as a template for several "log in" choices for a databse (don't get me started on how it could be better if you actually used a database program instead of Excel :banghead: )

I would like to have the options "Yes" or "No" changed to "Open a new record" and "Open an existing record" respectively, but seem to be having trouble getting it to work.

Any ideas?

Sub SELASecurity()
Application.ScreenUpdating = False
Answer = MsgBox("What would you like to open a new record?", vbYesNo, "Open SELA Records")
If Answer = vbNo Then
PWAnswer = Application.InputBox("Please enter the SELA Database password?")
If PWAnswer <> Sheets("Security").Range("B2") Then
Answer = MsgBox("The Password you have entered is incorrect.", vbOK, "ACCESS DENIED")

If Answer = vbOK Then Exit Sub

Else:
Sheets("FIM Complaint Database").Visible = True
Sheets("Review Existing Record").Select

End If
End If
If Answer = vbYes Then
PWAnswer = Application.InputBox("Please enter the SELA Database password?")
If PWAnswer <> Sheets("Security").Range("B2") Then
Answer = MsgBox("The Password you have entered is incorrect.", vbOK, "ACCESS DENIED")

If Answer = vbOK Then Exit Sub

Else:
Sheets("FIM Complaint Database").Visible = True
Sheets("New Record").Unprotect Password:=""
Sheets("New Record").Select
Range("C6") = "SELA"
Sheets("New Record").Protect Password:=""
End If
End If


'
End Sub

Thanks gents

Phel x

Bob Phillips
05-28-2009, 08:56 AM
You need to create a form with b uttons, not use MsgBox.

mdmackillop
05-28-2009, 09:06 AM
You can't change button titles on a standard messagebox. You could create a userfom to resemble one.

Here's a simplified form of your code which should be easier to follow. Please use Option Explicit and declare your variables.

Sub SELASecurity()
PWAnswer = Application.InputBox("Please enter the SELA Database password?")
If PWAnswer <> Sheets("Security").Range("B2") Then
MsgBox "The Password you have entered is incorrect." & vbCr & "ACCESS DENIED"
Exit Sub
End If
Application.ScreenUpdating = False
Answer = MsgBox("What would you like to open a new record?", vbYesNo, "Open SELA Records")
If Answer = vbNo Then
PWAnswer = Application.InputBox("Please enter the SELA Database password?")
If PWAnswer <> Sheets("Security").Range("B2") Then
Answer = MsgBox("The Password you have entered is incorrect.", vbOK, "ACCESS DENIED")
Exit Sub
Else
Sheets("FIM Complaint Database").Visible = True
Sheets("Review Existing Record").Select
End If
Else
Sheets("FIM Complaint Database").Visible = True
Sheets("New Record").Unprotect Password:=""
Sheets("New Record").Select
Range("C6") = "SELA"
Sheets("New Record").Protect Password:=""
End If
End Sub

Bob Phillips
05-28-2009, 09:33 AM
You can't change button titles on a standard messagebox.

You can, but it is very unstable.