Form unloads and closes workbook after making a selection

davidausten

New Member
Joined
Sep 1, 2017
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
Im using a form to make a selection from a workbook. It works fine for the first time but after you click the form and make a selection the form unloads on its own and the workbook closes. Also, if you try to open another workbook excel locks up and you have to close it with the task manager.

I need the form not to close after a selection as I intend to put a close button the form to let the user close it. Additionally I need the form and workbook up all day so the user can go back to it when they need to use it again. I intend to put a toggle button on the form to do that which I know how to do. I suspect that the combo box is where the problem lies just not sure how to fix it.

Below is all my code:

Workbook open

VBA Code:
Private Sub Workbook_Open()
Application.Visible = False
UserForm1.Show
End Sub

Close button on the form:

VBA Code:
Private Sub CommandButton1_Click()
Application.Visible = True
Unload Me
End Sub

Form Initialize:

VBA Code:
Private Sub UserForm_Initialize()
With UserForm1.ComboBox1
     .AddItem "Flat Payment"
     .AddItem "Duplicate Payment Invoice"
     .AddItem "Duplicate Payment Statement"
     .AddItem "Tax"
     .AddItem "Dispute Tax"
     .AddItem "Courtesy Adjustment"
     .AddItem "Added Payment to Misdirected"
     .AddItem "Transfer Portion of Payment"
     .AddItem "Transfer Payment Auto Lockbox"
     .AddItem "Transfer Payment Non Postables"
     .AddItem "Transfer Payment Related"
     .AddItem "Transfer Credit"
     .AddItem "Remit Request"
     .AddItem "Insufficient Remit"
     .AddItem "Transposition Error"
     .AddItem "Short Payment"
     .AddItem "Over Payment"
     .AddItem "Assigned to Staples"
End With
End Sub

Combo box where user makes selection at which point the form closes along with the workbook:

VBA Code:
Private Sub ComboBox1_Change()
Sheet1.Activate
Dim Decide As Integer
    Decide = Me.ComboBox1.ListIndex + 2
   
    Sheet1.Range("I" & Decide).Copy
   
End Sub




+-
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

davidausten

New Member
Joined
Sep 1, 2017
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
it works fine and the form stays put if the workbook isnt hidden on open or hidden at all
 

davidausten

New Member
Joined
Sep 1, 2017
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
i should clarify that when the form open on the workbook open event it works as expected that is hides the workbook and only the form shows which is what I want. But if you click outside the form excel and the form closes.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,557
Messages
5,625,500
Members
416,113
Latest member
Zulwaqar88

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