Pass MsgBox answer to a userform?

[TheWennerWoman]

Hello,

I have this

Dim Ans As VbMsgBoxResult
Const StrTitle As String = "Master Edit Tool"

If Sheet4.Range("BJ" & ActiveCell.Row).Value <> 0 Then
Ans = MsgBox ("The record you have selected to edit has multiple lines, any changes made here will affect all similar records!" & vbCr & vbCr & "Select Yes if you wish to do this or No if you only want to edit the one entry!", vbYesNo + vbQuestion, StrTitle)
End If

Load UserForm1
UserForm1.MultiPage1.Value = 0
UserForm1.Show

How can I pass "Ans" into the userform so that the code there knows what it is?

Many thanks.

 

[DanteAmor]

In the code of your userform.

Public ans As VbMsgBoxResult    'At the beginning of all the code

Try this:

  Dim ans As VbMsgBoxResult
  Const StrTitle As String = "Master Edit Tool"
  
  If Sheet4.Range("BJ" & ActiveCell.Row).Value <> 0 Then
    ans = MsgBox("The record you have selected to edit has multiple lines, any changes made here will affect all similar records!" & vbCr & vbCr & "Select Yes if you wish to do this or No if you only want to edit the one entry!", vbYesNo + vbQuestion, StrTitle)
  End If
  
  Load UserForm1
  With UserForm1
    .MultiPage1.Value = 0
    .ans = ans
    .Show
  End With

 

[dmt32]

Hi,
you could declare your variable as Public or maybe better to place it in the UserForms code page

In the TOP of your Userform

Public Ans As VbMsgBoxResult

Updated code

 Const StrTitle As String = "Master Edit Tool"
 
    Load UserForm1

    With UserForm1
    If Sheet4.Range("BJ" & ActiveCell.Row).Value <> 0 Then
        .Ans = MsgBox("The record you have selected to edit has multiple lines" & Chr(10) & _
                            "any changes made here will affect all similar records!" & _
                            vbCr & vbCr & "Select:-" & Chr(10) & _
                            "Yes -  if you wish to do this" & Chr(10) & _
                            "No - if you only want to edit the one entry!", vbYesNo + vbQuestion, StrTitle)
    End If
 
        .MultiPage1.Value = 0
        .Show
    End With

I trust you appreciate Ans will return either 6 or 7 ?

Hope Helpful

Dave

 

[TheWennerWoman]

Thanks all, I could only mark one answer as the solution even though both did the trick.

 

[DanteAmor]

Glad we could help & thanks for the feedback