Multiple Case statements

davidausten

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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,621
Office Version
2019
Platform
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,019
Office Version
2019
Platform
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
2016
Platform
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
2016
Platform
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,621
Office Version
2019
Platform
Windows

ADVERTISEMENT

Did Dave's suggestion work any better?
 

davidausten

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,110
Messages
5,509,266
Members
408,719
Latest member
padapinto

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top