Hiding a workbook causes problems with user form

davidausten

New Member
Joined
Sep 1, 2017
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
I have a request from a user to only show the userform on startup. So I used the following code in my Workbook Open

VBA Code:
Application.View = False
UserForm1.Show

Here is the full userform code

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

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

Private Sub Label1_Click()

End Sub

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

Private Sub ComboBox1_Change()
Dim Decide As Integer

Decide = Me.ComboBox1.ListIndex + 2

Sheet1.Range("I" & Decide).Copy


End Sub

This seems to have affected the function of the userform as it doesnt perform the necessary functions when the workbook is hidden.

Does anyone have any ideas?

I also get a compile error "Ambiguous name" on this line

VBA Code:
Private Sub ComboBox1_Change
 
Last edited:

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,371
Office Version
  1. 2019
Platform
  1. Windows
Do you mean?
Rich (BB code):
Application.Visible = False

Dave
 
Last edited:

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,371
Office Version
  1. 2019
Platform
  1. Windows
I also get a compile error "Ambiguous name" on this line

VBA Code:
Private Sub ComboBox1_Change

You have a duplicate of that event in your forms code page - deleting one of them should resolve the issue

Dave
 

davidausten

New Member
Joined
Sep 1, 2017
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
Thanks Dave...Ive changed my forms code and here it is in its entirety....

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

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

The issue is when you click an item from the list it does not copy the cell contents. Do I need to activate Sheet1 at the beginning of the Combo sub?
 

davidausten

New Member
Joined
Sep 1, 2017
Messages
35
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I got it to work by putting Sheet1.Activate at the beginning of the sub, Hiding should have no effect on it working I assume..
 

davidausten

New Member
Joined
Sep 1, 2017
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
Hello Dave...Ive another problem with this and was hoping you could take a peek. I started a new thread for it here

 

Watch MrExcel Video

Forum statistics

Threads
1,122,459
Messages
5,596,270
Members
414,049
Latest member
MisterExcel26

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