Multiple Case statements

davidausten

New Member
Joined
Sep 1, 2017
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,853
Office Version
  1. 2019
Platform
  1. Windows
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.
VBA Code:
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
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,099
Office Version
  1. 2019
Platform
  1. Windows
Try following & see if helps

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

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

End Sub

Dave
 

davidausten

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

ADVERTISEMENT

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

New Member
Joined
Sep 1, 2017
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
should have said it wont undo the copy of the first one you chose to let you select another.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,853
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Did Dave's suggestion work any better?
 

davidausten

New Member
Joined
Sep 1, 2017
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,099
Office Version
  1. 2019
Platform
  1. Windows
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

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,258
Messages
5,527,660
Members
409,781
Latest member
Maxcwy2020

This Week's Hot Topics

Top