Need help consolidating code

[andrewb90]

Hi all,

I have this code:

Private Sub CommandButton1_Click() 'If Sheets("ValuesG").Range("A2").Value = "1" Then
    MB01.TextBox1.Text = Sheets("ValuesG").Range("B5").Text
    MB01.CommandButton1.Caption = "Continue"
    MB01.CommandButton2.Caption = ""
    Sheets("ValuesG").Range("A2").Value = "2"
ElseIf Sheets("ValuesG").Range("A2").Value = "2" Then
    Application.Run "Module6.mb6"
    MB01.TextBox1.Text = Sheets("ValuesG").Range("B6").Text
    MB01.CommandButton1.Caption = "Continue"
    MB01.CommandButton2.Caption = ""
    Sheets("ValuesG").Range("A2").Value = "3"
ElseIf Sheets("ValuesG").Range("A2").Value = "3" Then
    MB01.TextBox1.Text = Sheets("ValuesG").Range("B7").Text
    MB01.CommandButton1.Caption = "Continue"
    MB01.CommandButton2.Caption = ""

The series continues on for 30 more rows on the spreadsheet. My only challenge is that certain parts require an application.run (and there isn't a set pattern for those)

Any help or advice would be greatly appreciated.

 

[Yongle]

To avoid endless repetition
- replace IF ... End If with Select Case
- use With ... End With when referring to same object multiple times
- use variables
- some of the variables are strings and the values are attributed to objects at the end of Select Case
- some of the variables are objects and the values are attributed immediately

Select Case tests in sequence and will exit Select Case as soon as a test is true -put the most likely outcomes first,second third etc to speed the code

Dim  [COLOR=#ff0000]TB1[/COLOR]  As S tring,[COLOR=#ff0000] CB1[/COLOR] As Str ing, [COLOR=#ff0000]CB2[/COLOR] As String, [COLOR=#ff0000]A2[/COLOR] As Range, [COLOR=#ff0000]wsG[/COLOR]  As Worksheet
    Set wsG = Sheets("ValuesG")
    Set A2 = wsG.Range("A2")
    
    [COLOR=#ff0000]Select Case[/COLOR] A2.Value
        Case "1"
            TB1 = wsG.Range("B5").Text
            CB1 = "Continue"
            CB2 = ""
            A2 = "2"
        Case "2"
            Application.Run "Module6.mb6"
            TB1 = wsG.Range("B6").Text
            CB1 = "Continue"
            CB2 = ""
            A2 = "3"
        Case "3"
            TB1 = wsG.Range("B7").Text
            CB1 = "Continue"
            CB2 = ""
            A2 = "4"
        Case Else [I][COLOR=#006400]'my added example for illustration[/COLOR][/I]
            TB1 = wsG.Range("B10").Text
            CB1 = ""
            CB2 = "Continue"
            A2 = "0"
    [COLOR=#ff0000]End Select[/COLOR]
    
    [COLOR=#ff0000]With[/COLOR] MB01
        .TextBox1.Text = TB1
        .CommandButton1.Caption = CB1
        .CommandButton2.Caption = CB2
    [COLOR=#ff0000]End With[/COLOR]

Exploit any patterns
- CB1 and CB2 are usually the same values
- A2 is usualy set to its previous value + 1

Numbers can generally be referred to without wrapping in quote marks(2 instead of "2")
- the code below shows that method purely for illustration
- it will not always work
- there are situations when numbers are required to be treated as strings

    Dim TB1 As String, CB1 As String, CB2 As String, A2 [COLOR=#006400]As Long[/COLOR], wsG As Worksheet
    Set wsG = Sheets("ValuesG")
    A2 = wsG.Range("A2").Value + 1
    
[COLOR=#ff0000]    CB1 [/COLOR]= "Continue"[COLOR=#ff0000]
    CB2[/COLOR] = ""[COLOR=#ff0000]
    A2 [/COLOR]= A2 + 1
    Select Case wsG.Range("A2").Value
        Case [COLOR=#008080]1[/COLOR]
            TB1 = wsG.Range("B5").Text
        Case [COLOR=#008080]2[/COLOR]
            Application.Run "Module6.mb6"
            TB1 = wsG.Range("B6").Text
        Case [COLOR=#008080]3[/COLOR]
            TB1 = wsG.Range("B7").Text
        Case Else[COLOR=#006400][I] 'my added example for illustration[/I][/COLOR]
            TB1 = wsG.Range("B10").Text
            [COLOR=#ff0000]CB1[/COLOR] = ""
            [COLOR=#ff0000]CB2[/COLOR] = "Continue"
            [COLOR=#ff0000]A2[/COLOR] = 0
    End Select
    
    With MB01
        .TextBox1.Text = [COLOR=#ff0000]TB1[/COLOR]
        .CommandButton1.Caption = [COLOR=#ff0000]CB1[/COLOR]
        .CommandButton2.Caption = [COLOR=#ff0000]CB2[/COLOR]
    End With
    wsG.Range("A2").Value = A2

 

[AlphaFrog]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click() 
    [COLOR=darkblue]Dim[/COLOR] v [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]With[/COLOR] Sheets("ValuesG")
        v = .Range("A2").Value
        [COLOR=darkblue]If[/COLOR] v > 0 [COLOR=darkblue]Then[/COLOR]
            MB01.TextBox1.Text = .Range("B" & 4 + v).Text
            MB01.CommandButton1.Caption = "Continue"
            MB01.CommandButton2.Caption = ""
             .Range("A2").Value = v + 1
            [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] v
                [COLOR=darkblue]Case[/COLOR] 2, 3, 5, 7, 11, 13, 17, 19, 23, 29
                    Application.Run "Blah"
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]