Hiding a workbook causes problems with user form

[davidausten]

I have a request from a user to only show the userform on startup. So I used the following code in my Workbook Open

Application.View = False
UserForm1.Show

Here is the full userform 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

Private Sub ComboBox1_Change

 

[dmt32]

Do you mean?

Application.Visible = False

Dave

 

[dmt32]

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

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]

Thanks Dave...Ive changed my forms code and here it is in its entirety....

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]

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..

 

[dmt32]

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..

If you have more than one worksheet in your workbook then qualifying the references is considered good practice

you can read more here:excel-vba - Qualifying References | excel-vba Tutorial

Dave

 

[davidausten]

Hello Dave...Ive another problem with this and was hoping you could take a peek. I started a new thread for it here

Form unloads and closes workbook after making a selection

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...

www.mrexcel.com

 

[davidausten]

bump