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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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,155
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,155

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,155

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,155
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,827
Messages
5,855,878
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top