Multiple Case statements

[davidausten]

Im using a combo box to select the option. The thing I am having trouble with is that if you select the second case statement from the list (they are in this order in the combo box) it selects I2 and not I3. totally stumped.

Private Sub ComboBox1_Change()
With ComboBox1

Select Case Decide

Case Me.ComboBox1.Value = "Flat_Payment"

Sheet1.Range("I2").Copy

Case Me.ComboBox1.Value = "Duplicate_Payment_Invoice"

Sheet1.Range("I3").Copy

Case Me.ComboBox1.Value = "Duplicate_Payment_Statement"

Sheet1.Range("I4").Copy

Case Me.ComboBox1.Value = "Tax"

Sheet1.Range("I5").Copy

Case Me.ComboBox1.Value = "Dispute_Tax"

Sheet1.Range("I6").Copy

Case Me.ComboBox1.Value = "Courtesy_Adjustment"

Sheet1.Range("I7").Copy

Case Me.ComboBox1.Value = "Added_Payment_to_Misdirected"

Sheet1.Range("I8").Copy

Case Me.ComboBox1.Value = "Transfer_Portion_of_Payment"

Sheet1.Range("I9").Copy

Case Me.ComboBox1.Value = "Transfer_Payment_Auto_Lockbox"

Sheet1.Range("I10").Copy

Case Me.ComboBox1.Value = "Transfer_Payment_Non_Postables"

Sheet1.Range("I11").Copy

Case Me.ComboBox1.Value = "Transfer_Payment_Related"

Sheet1.Range("I12").Copy

Case Me.ComboBox1.Value = "Transfer_Credit"

Sheet1.Range("I13").Copy

Case Me.ComboBox1.Value = "Remit_Request"

Sheet1.Range("I14").Copy

Case Me.ComboBox1.Value = "Insufficient_Remit"

Sheet1.Range("I15").Copy

Case Me.ComboBox1.Value = "Transposition_Error"

Sheet1.Range("I16").Copy

Case Me.ComboBox1.Value = "Short_Payment"

Sheet1.Range("I17").Copy

Case Me.ComboBox1.Value = "Over_Payment"

Sheet1.Range("I18").Copy

Case Me.ComboBox1.Value = "Assigned_to_Staples"

Sheet1.Range("I19").Copy


End Select

End With

End Sub

 

[jasonb75]

As far as I can see, your 'With' block is serving no purpose and 'Decide' has no meaning.

First 2 corrected to give you an idea, just follow the same pattern for the rest.

Private Sub ComboBox1_Change()
Select Case ComboBox1.Value
    Case "Flat_Payment"
        Sheet1.Range("I2").Copy
    Case "Duplicate_Payment_Invoice"
        Sheet1.Range("I3").Copy
End Select
End Sub

 

[davidausten]

thanks ill give it a go

 

[dmt32]

Try following & see if helps

Private Sub ComboBox1_Change()
    Dim Decide As Integer
    
    Decide = Me.ComboBox1.ListIndex + 2

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

End Sub

Dave

 

[davidausten]

that gave me what i wanted but say you select
Case "Flat_Payment" but you didnt want that so you choose another. the focus is still on Flat Payment so if you choose another it wont copy. Is there a way around?

 

[davidausten]

should have said it wont undo the copy of the first one you chose to let you select another.

 

[jasonb75]

Did Dave's suggestion work any better?

 

[davidausten]

jason..i havent tried Daves but I will. So like I said yours and Daves appear to go to the cell and copy as you would expect. The thing is now the next time the user selects another from the list "copy" is still selected from the previous cell because you didnt "paste" . In fact the intent of this is to find the right cell copy it then go to a web page and paste it. So you see there is never a "paste" inside excel to clear the copy. Hope that makes sense. Or could you have some sort of button that would clear the previous copy? Not sure if thats possible or not.

 

[dmt32]

jason..i havent tried Daves but I will. So like I said yours and Daves appear to go to the cell and copy as you would expect. The thing is now the next time the user selects another from the list "copy" is still selected from the previous cell because you didnt "paste" .

You should just need to clear the clipboard

Private Sub ComboBox1_Change()
    Dim Decide As Integer
   
    Decide = Me.ComboBox1.ListIndex + 2
    Application.CutCopyMode = False
    Sheet1.Range("I" & Decide).Copy

End Sub

try adding line in bold & see if resolves issue

Dave

 

[davidausten]

ill give that a try.