Close one form and open another

keevans

Board Regular
Joined
Aug 22, 2013
Messages
77
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?

Code:
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,037
Office Version
  1. 365
Platform
  1. Windows
What error do you get? and on which line?

You should at least hide/unload AlternateAddressForm before showing LastOriginatorForm
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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

Board Regular
Joined
Aug 22, 2013
Messages
77
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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154

ADVERTISEMENT

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

Board Regular
Joined
Aug 22, 2013
Messages
77
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!

Code:
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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154

ADVERTISEMENT

Hi Keely,

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

Mark
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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.

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,818
Messages
5,525,077
Members
409,618
Latest member
gkllc

This Week's Hot Topics

Top