Use a module-level variable m_ReturnValue to indicate the button the user clicked. Set this to vbCancel before displaying the form. Change it if the user clicks another button.

Note: If you really want this to behave like the Common Dialog Control, you can make an ActiveX control. Give it a form and make it display the form as described here. This will prevent the program from displaying the form using the Show method.

For more information on building custom controls, see my book Custom Controls Library.

Private m_ReturnValue As Integer

' Display the dialog. Return vbOk or vbCancel
' to indicate which button the user pressed.
Public Function ShowDialog(Optional prompt As String = "") _
    As Integer
    ' Assume the user will cancel.
    m_ReturnValue = vbCancel

    ' Display the dialog modally.
    txtPrompt.Text = prompt
    Show vbModal

    ' Set the return value.
    ShowDialog = m_ReturnValue

    ' Unload.
    Unload Me
End Function

' Just hide the form.
Private Sub cmdCancel_Click()
    Me.Hide
End Sub

' Set the return value and hide the form.
Private Sub cmdOk_Click()
    m_ReturnValue = vbOK
    Me.Hide
End Sub