Msgbox yes / no code advice please

[ipbr21054]

Morning,
Im having an issue trying to get this to work correctly but now im confused with what ive done.
The code i have in use is supplied below.

This is how it should work.

I open the worksheet & the Msgbox reads ADD CUSTOMER TO WORKSHEET
Yes should then DatabaseUserForm.Show
No should then DatabaseNameSearch.Show

My issues are when closing a form the other is shown or if i select Yes then nothing is shown at all.

Private Sub Worksheet_Activate()
Range("A5").Select
Range("A6").Select
Dim answer As Integer
 
answer = MsgBox("ADD NEW CUSTOMERS NAME TO WORKSHEET ?", vbQuestion + vbYesNo + vbDefaultButton2, "DATABASE NEW CUSTOMERS NAME MESSAGE")
If answer = vbNo Then
answer = MsgBox("SEARCH FOR CUSTOMER IN WORKSHEET ?", vbQuestion + vbYesNo + vbDefaultButton2, "DATABASE SEARCH FOR CUSTOMER MESSAGE")
Else
DatabaseUserForm.Show
If answer = vbYes Then
DatabaseNameSearch.Show
Else
DatabaseUserForm.Show
End If
End If
    
End Sub

 

[jasonb75]

See if this does what you need

Private Sub Worksheet_Activate()
Select Case MsgBox "ADD NEW CUSTOMERS NAME TO WORKSHEET ?", vbQuestion + vbYesNo + vbDefaultButton2, "DATABASE NEW CUSTOMERS NAME MESSAGE"
    Case vbNo
        DatabaseUserForm.Show
    Case vbYes
        DatabaseNameSearch.Show
End Select
End Sub

 

[ipbr21054]

Hi,
Select Case line is in Red

 

[Yongle]

How about

Private Sub Worksheet_Activate()
 
If MsgBox("ADD NEW CUSTOMERS NAME TO WORKSHEET ?", vbYesNo, "DATABASE NEW CUSTOMERS NAME MESSAGE") = vbYes Then
    DatabaseUserForm.Show
Else
    If MsgBox("SEARCH FOR CUSTOMER IN WORKSHEET ?", vbYesNo, "DATABASE SEARCH FOR CUSTOMER MESSAGE") = vbYes Then DataBaseNameSearch.Show
End If
    
End Sub

 

[ipbr21054]

The second code worked BUT see my long winded working code whilst waiting for a reply.

Private Sub Worksheet_Activate()
Range("A5").Select
Range("A6").Select
Dim answer As Integer
 
answer = MsgBox("ADD NEW CUSTOMERS NAME TO WORKSHEET ?", vbQuestion + vbYesNo + vbDefaultButton2, "DATABASE NEW CUSTOMERS NAME MESSAGE")
If answer = vbYes Then
DatabaseUserForm.Show
Else
If answer = vbNo Then
answer = MsgBox("SEARCH FOR CUSTOMER IN WORKSHEET ?", vbQuestion + vbYesNo + vbDefaultButton2, "DATABASE SEARCH FOR CUSTOMER MESSAGE")
If answer = vbYes Then
DatabaseNameSearch.Show
Else
If answer = cbNo Then
Exit Sub
End If
End If
End If
End If
End Sub

 

[jasonb75]

Select Case line is in Red

Oops, my bad, I shouldn't have removed the parentheses

Private Sub Worksheet_Activate()
Select Case MsgBox("ADD NEW CUSTOMERS NAME TO WORKSHEET ?", vbQuestion + vbYesNo + vbDefaultButton2, "DATABASE NEW CUSTOMERS NAME MESSAGE")
    Case vbNo
        DatabaseUserForm.Show
    Case vbYes
        DatabaseNameSearch.Show
End Select
End Sub

What should happen if the user tries to close the message box without selecting yes or no?