Close one form and open another

[keevans]

Hi, hoping someone can help! I'm stuck. I have a form "AlternateAddressForm". Once the user has completed the form I want them to be prompted if they want to add another alternate address. If they select "Yes" I want the AlternateAddressForm to show again so they can make another entry. If they say "No" I want the form to close and the "LastOriginatorForm" to show. I'm getting a bug when I run. See my code below. Does anyone have any suggestions?

If MsgBox("Do you want to add/delete another Alternate Address Contact?", vbYesNo, "Add/delete Alternate Address Contact?") = vbNo Then
    AlternateAddressForm.Show
Else
    LastOriginatorForm.Show
End If

Thank you!

Keely

 

[Momentman]

What error do you get? and on which line?

You should at least hide/unload AlternateAddressForm before showing LastOriginatorForm

 

[GTO]

After the user enters the first address, what causes the entered information to update to somewhere, and is that somewhere someplace on a worksheet? Just based on your question thus far, I would think in terms of a button click after entering an address, and either storing the data in an array or being written to a worksheet, then the msgbox. Selecting the Yes button <YES>(to the question, "add another...?" would simply result in the box being cleared for another address to be typed in.

Mark

 

[keevans]

The data is unloaded into the worksheet and then the msgbox appears.

The error I get is "Compile error: Invalid use of property"

After the user enters the first address, what causes the entered information to update to somewhere, and is that somewhere someplace on a worksheet? Just based on your question thus far, I would think in terms of a button click after entering an address, and either storing the data in an array or being written to a worksheet, then the msgbox. Selecting the Yes button <yes>(to the question, "add another...?" would simply result in the box being cleared for another address to be typed in.

Mark

</yes>

 

[GTO]

Hi, hoping someone can help! I'm stuck. I have a form "AlternateAddressForm". Once the user has completed the form I want them to be prompted if they want to add another alternate address. If they select "Yes" I want the AlternateAddressForm to show again so they can make another entry. If they say "No" I want the form to close and the "LastOriginatorForm" to show. I'm getting a bug when I run. See my code below. Does anyone have any suggestions?

The data is unloaded into the worksheet and then the msgbox appears.

The error I get is "Compile error: Invalid use of property"

</YES>

Hi Keely,

I think you will need to show us more of the code, or upload the workbook to some share site. I am not sure of where you have the code snippet you showed. I first assumed it was part of the button's (still assuming a button?) code that updates the sheet with the current values? But this would get an 'already displayed' error, at least presuming the form is modal. Also, though you state if they select Yes... but it seems to be if the user selects No, that would display or redisplay the form.

I hope we are able to help,

Mark

 

[keevans]

Hi Mark,

It would be part of the NextCmd command button. I've copied the full code below. The code I had originally copied was at the very end of the Sub. Thanks for the help!

Private Sub NextCmd_Click()
 
LastColumn = Sheets("Form4164").Range("A13").End(xlToRight).Column
LastRow = Sheets("Form4164").Cells(22, LastColumn).End(xlDown).Row + 1

'Error messaging
If ActionCombo.Text = "" Then
    CreateObject("WScript.Shell").Popup "You must enter an Action", 2, "FYI"
    Exit Sub
ElseIf NameTxt.Text = "" Then
    CreateObject("WScript.Shell").Popup "You must enter an Alternate Contact Name", 2, "FYI"
    Exit Sub
ElseIf EmailTxt.Text = "" Then
    CreateObject("WScript.Shell").Popup "You must enter an Alternate Contact Email", 2, "FYI"
    Exit Sub
End If

'Unload account

Rows(LastRow).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Form4164").Cells(LastRow, LastColumn).Value = AlternateAddressForm.ActionCombo.Text
Sheets("Form4164").Cells(LastRow + 1, LastColumn).Value = AlternateAddressForm.NameTxt.Text
Sheets("Form4164").Cells(LastRow + 2, LastColumn).Value = AlternateAddressForm.EmailTxt.Text
Unload AlternateAddressForm
 
Sheets("Form4164").Cells(LastRow, 1).Value = "Alternate Contact - Add/Delete"
Sheets("Form4164").Cells(LastRow + 1, 1).Value = "Alternate Contact Name"
Sheets("Form4164").Cells(LastRow + 2, 1).Value = "Alternate Contact Email"

Range(Cells(22, 1), Cells(24, 1)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range(Cells(LastRow, 1), Cells(LastRow + 2, 1)).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
 
If MsgBox("Do you want to add/delete another Alternate Address Contact?", vbYesNo, "Add/delete Alternate Address Contact?") = vbNo Then
    AlternateAddressForm.Show
Else
    End
End If
End Sub

 

[GTO]

Hi Keely,

Just to be sure, Private Sub NextCmd_Click() resides in the UserForm named "AlternateAddressForm", right?

Mark

 

[keevans]

Correct!

 

[GTO]

Thank you for that clarification. I didn't do much, and actually, while I was half expecting a glitch where you reload the form, it seemed to run for me. Anyways, rather than unload the form, maybe just empty the values from the text and combo boxes and redisplay the form if wanted.

Option Explicit
  
Private Sub NextCmd_Click()
Dim LastColumn As Long
Dim LastRow As Long
    
    LastColumn = Sheets("Form4164").Range("A13").End(xlToRight).Column
    LastRow = Sheets("Form4164").Cells(22, LastColumn).End(xlDown).Row + 1
    
    'Error messaging
    If ActionCombo.Text = "" Then
        CreateObject("WScript.Shell").Popup "You must enter an Action", 2, "FYI"
        Exit Sub
    ElseIf NameTxt.Text = "" Then
        CreateObject("WScript.Shell").Popup "You must enter an Alternate Contact Name", 2, "FYI"
        Exit Sub
    ElseIf EmailTxt.Text = "" Then
        CreateObject("WScript.Shell").Popup "You must enter an Alternate Contact Email", 2, "FYI"
        Exit Sub
    End If
    
    'Unload account
    Rows(LastRow).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Sheets("Form4164").Cells(LastRow, LastColumn).Value = AlternateAddressForm.ActionCombo.Text
    Sheets("Form4164").Cells(LastRow + 1, LastColumn).Value = AlternateAddressForm.NameTxt.Text
    Sheets("Form4164").Cells(LastRow + 2, LastColumn).Value = AlternateAddressForm.EmailTxt.Text
    
    '***Rather than Unload the form, just hide it and clear the values'
    Me.Hide
    DoEvents
    
    Me.ActionCombo.ListIndex = -1
    Me.EmailTxt.Value = vbNullString
    Me.NameTxt.Value = vbNullString
    
    'Unload AlternateAddressForm <---See below in MsgBox
     
    Sheets("Form4164").Cells(LastRow, 1).Value = "Alternate Contact - Add/Delete"
    Sheets("Form4164").Cells(LastRow + 1, 1).Value = "Alternate Contact Name"
    Sheets("Form4164").Cells(LastRow + 2, 1).Value = "Alternate Contact Email"
    
    Range(Cells(22, 1), Cells(24, 1)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range(Cells(LastRow, 1), Cells(LastRow + 2, 1)).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    
    If MsgBox("Do you want to add/delete another Alternate Address Contact?", _
              vbYesNo Or vbQuestion, _
              "Add/delete Alternate Address Contact?" _
              ) = vbYes Then
        
        AlternateAddressForm.Show
        
    Else
        Unload Me
    End If
    
End Sub

I would mention that of course as I cannot see your actual workbook, I may well be missing something. That said, I am curious as to why we are doing all the selecting and copying?

Hope that helps a little at least,

Mark