A Better way in VBA?

T

tmanger

Board Regular
Joined
Jul 15, 2002
Messages
101
Good day to all.

I am in the midst of designing a userform that contains a combobox and 4 textboxes. The combobox range is a list of nutrients from a food database, and the textboxes display the sum total of the nutrient chosen for Breakfast, Lunch, Dinner, and the Daily Total which are calculated on a separate worksheet and displayed in the textboxes. This is the code that I currently have (my apologies for the length):

Code: 
Private Sub ComboBox1_Change()
    Select Case ComboBox1.Value
        Case "Ash (g)"
            BreakSum1.Text = Worksheets("Food Diary").Range("AJ22")
            LunchSum1.Text = Worksheets("Food Diary").Range("AJ41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("AJ60")
            DailySum1.Text = Worksheets("Food Diary").Range("AJ63")
        Case "Fiber (g)"
            BreakSum1.Text = Worksheets("Food Diary").Range("AK22")
            LunchSum1.Text = Worksheets("Food Diary").Range("AK41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("AK60")
            DailySum1.Text = Worksheets("Food Diary").Range("AK63")
        Case "Total Sugars (g)"
            BreakSum1.Text = Worksheets("Food Diary").Range("AL22")
            LunchSum1.Text = Worksheets("Food Diary").Range("AL41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("AL60")
            DailySum1.Text = Worksheets("Food Diary").Range("AL63")
        Case "Calcium (mg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("AM22")
            LunchSum1.Text = Worksheets("Food Diary").Range("AM41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("AM60")
            DailySum1.Text = Worksheets("Food Diary").Range("AM63")
        Case "Iron (mg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("AN22")
            LunchSum1.Text = Worksheets("Food Diary").Range("AN41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("AN60")
            DailySum1.Text = Worksheets("Food Diary").Range("AN63")
        Case "Magnesium (mg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("AO22")
            LunchSum1.Text = Worksheets("Food Diary").Range("AO41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("AO60")
            DailySum1.Text = Worksheets("Food Diary").Range("AO63")
        Case "Phosphate (mg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("AP22")
            LunchSum1.Text = Worksheets("Food Diary").Range("AP41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("AP60")
            DailySum1.Text = Worksheets("Food Diary").Range("AP63")
        Case "Potassium (mg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("AQ22")
            LunchSum1.Text = Worksheets("Food Diary").Range("AQ41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("AQ60")
            DailySum1.Text = Worksheets("Food Diary").Range("AQ63")
        Case "Sodium (mg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("AR22")
            LunchSum1.Text = Worksheets("Food Diary").Range("AR41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("AR60")
            DailySum1.Text = Worksheets("Food Diary").Range("AR63")
        Case "Zinc (mg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("AS22")
            LunchSum1.Text = Worksheets("Food Diary").Range("AS41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("AS60")
            DailySum1.Text = Worksheets("Food Diary").Range("AS63")
        Case "Copper (mg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("AT22")
            LunchSum1.Text = Worksheets("Food Diary").Range("AT41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("AT60")
            DailySum1.Text = Worksheets("Food Diary").Range("AT63")
        Case "Manganese (mg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("AU22")
            LunchSum1.Text = Worksheets("Food Diary").Range("AU41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("AU60")
            DailySum1.Text = Worksheets("Food Diary").Range("AU63")
        Case "Selenium (µg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("AV22")
            LunchSum1.Text = Worksheets("Food Diary").Range("AV41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("AV60")
            DailySum1.Text = Worksheets("Food Diary").Range("AV63")
        Case "Vit C (mg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("AW22")
            LunchSum1.Text = Worksheets("Food Diary").Range("AW41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("AW60")
            DailySum1.Text = Worksheets("Food Diary").Range("AW63")
        Case "Thiamine (mg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("AX22")
            LunchSum1.Text = Worksheets("Food Diary").Range("AX41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("AX60")
            DailySum1.Text = Worksheets("Food Diary").Range("AX63")
        Case "Riboflavin (mg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("AY22")
            LunchSum1.Text = Worksheets("Food Diary").Range("AY41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("AY60")
            DailySum1.Text = Worksheets("Food Diary").Range("AY63")
        Case "Niacin (mg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("AZ22")
            LunchSum1.Text = Worksheets("Food Diary").Range("AZ41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("AZ60")
            DailySum1.Text = Worksheets("Food Diary").Range("AZ63")
        Case "Pantothenic Acid (mg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("BA22")
            LunchSum1.Text = Worksheets("Food Diary").Range("BA41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("BA60")
            DailySum1.Text = Worksheets("Food Diary").Range("BA63")
        Case "Vit B6 (mg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("BB22")
            LunchSum1.Text = Worksheets("Food Diary").Range("BB41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("BB60")
            DailySum1.Text = Worksheets("Food Diary").Range("BB63")
        Case "Folate (µg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("BC22")
            LunchSum1.Text = Worksheets("Food Diary").Range("BC41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("BC60")
            DailySum1.Text = Worksheets("Food Diary").Range("BC63")
        Case "Vit B12 (µg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("BD22")
            LunchSum1.Text = Worksheets("Food Diary").Range("BD41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("BD60")
            DailySum1.Text = Worksheets("Food Diary").Range("BD63")
        Case "Vit A (IU)"
            BreakSum1.Text = Worksheets("Food Diary").Range("BE22")
            LunchSum1.Text = Worksheets("Food Diary").Range("BE41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("BE60")
            DailySum1.Text = Worksheets("Food Diary").Range("BE63")
        Case "Retinol (µg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("BF22")
            LunchSum1.Text = Worksheets("Food Diary").Range("BF41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("BF60")
            DailySum1.Text = Worksheets("Food Diary").Range("BF63")
        Case "Vit E (IU)"
            BreakSum1.Text = Worksheets("Food Diary").Range("BG22")
            LunchSum1.Text = Worksheets("Food Diary").Range("BG41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("BG60")
            DailySum1.Text = Worksheets("Food Diary").Range("BG63")
        Case "Vit K (µg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("BH22")
            LunchSum1.Text = Worksheets("Food Diary").Range("BH41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("BH60")
            DailySum1.Text = Worksheets("Food Diary").Range("BH63")
        Case "Alpha Carotene (µg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("BI22")
            LunchSum1.Text = Worksheets("Food Diary").Range("BI41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("BI60")
            DailySum1.Text = Worksheets("Food Diary").Range("BI63")
        Case "Beta Carotene (µg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("BJ22")
            LunchSum1.Text = Worksheets("Food Diary").Range("BJ41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("BJ60")
            DailySum1.Text = Worksheets("Food Diary").Range("BJ63")
        Case "Lycopene (µg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("BK22")
            LunchSum1.Text = Worksheets("Food Diary").Range("BK41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("BK60")
            DailySum1.Text = Worksheets("Food Diary").Range("BK63")
        Case "Lutein (µg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("BL22")
            LunchSum1.Text = Worksheets("Food Diary").Range("BL41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("BL60")
            DailySum1.Text = Worksheets("Food Diary").Range("BL63")
        Case "Saturated Fat (g)"
            BreakSum1.Text = Worksheets("Food Diary").Range("BM22")
            LunchSum1.Text = Worksheets("Food Diary").Range("BM41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("BM60")
            DailySum1.Text = Worksheets("Food Diary").Range("BM63")
        Case "Mono-Unsaturated Fat (g)"
            BreakSum1.Text = Worksheets("Food Diary").Range("BN22")
            LunchSum1.Text = Worksheets("Food Diary").Range("BN41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("BN60")
            DailySum1.Text = Worksheets("Food Diary").Range("BN63")
        Case "Poly-Unsaturated Fat (g)"
            BreakSum1.Text = Worksheets("Food Diary").Range("BO22")
            LunchSum1.Text = Worksheets("Food Diary").Range("BO41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("BO60")
            DailySum1.Text = Worksheets("Food Diary").Range("BO63")
        Case "Cholesterol (mg)"
            BreakSum1.Text = Worksheets("Food Diary").Range("BP22")
            LunchSum1.Text = Worksheets("Food Diary").Range("BP41")
            DinnerSum1.Text = Worksheets("Food Diary").Range("BP60")
            DailySum1.Text = Worksheets("Food Diary").Range("BP63")
    
    End Select

End Sub

Is there a shorter, more effecient way to set this up? I ask this because I now want to format the numbers in the textbox, but find that I have to alter every single case statement!

Many thanks in advance.

Tom. :oops:
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Thanks for the quick reply. Don't know/think I can do that within the userform and VBA.

Tom.
 
Upvote 0
You are doing the exact same thing for every case, the only difference being the column for each. With that in mind, just use the Select Case to determine the column to use, then just set the breakfast/lunch/dinner values at one go:

Code: 
Dim myCol As Integer

'uses the select Case statment to set the column number to use
Select Case ComboBox1.Value
    Case "Ash (g)": myCol = 36 'column AJ
    Case "Fiber (g)": myCol = 37 'col AK
    Case "Total Sugars (g)": myCol = 38 'col AL
    Case "Calcium (mg)": myCol = 39 'col AM
    Case "Iron (mg)": myCol = 40 'col AN
    Case "Magnesium (mg)": myCol = 41 'col AO
    Case "Phosphate (mg)": myCol = 42 'col AP
    Case "Potassium (mg)": myCol = 43 'col AQ
    Case "Sodium (mg)": myCol = 44 'col AR
    Case "Zinc (mg)": myCol = 45 'col AS
    Case "Copper (mg)": myCol = 46 'col AT
    Case "Manganese (mg)": myCol = 47 'col AU
    Case "Selenium (µg)": myCol = 48 'col AV
    Case "Vit C (mg)": myCol = 49 'col AW
    Case "Thiamine (mg)": myCol = 50 'col AX
    Case "Riboflavin (mg)": myCol = 51 'col AY
    Case "Niacin (mg)": myCol = 52 'col AZ
    Case "Pantothenic Acid (mg)": myCol = 53 'col BA
    Case "Vit B6 (mg)": myCol = 54 'col BB
    Case "Folate (µg)": myCol = 55 'col BC
    Case "Vit B12 (µg)": myCol = 56 'col BD
    Case "Vit A (IU)": myCol = 57 'col BE
    Case "Retinol (µg)": myCol = 58 'col BF
    Case "Vit E (IU)": myCol = 59 'col BG
    Case "Vit K (µg)": myCol = 60 'col BH
    Case "Alpha Carotene (µg)": myCol = 61 'col BI
    Case "Beta Carotene (µg)": myCol = 62 'col BJ
    Case "Lycopene (µg)": myCol = 63 'col BK
    Case "Lutein (µg)": myCol = 64 'col BL
    Case "Saturated Fat (g)": myCol = 65 'col BM
    Case "Mono-Unsaturated Fat (g)": myCol = 66 'col BN
    Case "Poly-Unsaturated Fat (g)": myCol = 67 'col BO
    Case "Cholesterol (mg)": myCol = 68 'col BP
End Select

'
With Sheets("Food Diary")
    BreakSum1.Text = .Cells(22, myCol).Text
    LunchSum1.Text = .Cells(41, myCol).Text
    DinnerSum1.Text = .Cells(60, myCol).Text
    DailySum1.Text = .Cells(63, myCol).Text
End With
 
Upvote 0
Thank you, Von Pookie!

Much cleaner, now I can simply format the last 4 lines where the textboxes are populated. Figured there had to be an easier way.....

Cheers,

Tom.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,430
Messages
6,124,849
Members
449,194
Latest member
HellScout

Share this page

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
Back
Top