Shorten Code

H

HYKE

Active Member
Joined
Jan 31, 2010
Messages
373
Hi,

Can this code be shortened? If so, how?
Code: 
Private Sub OptionButton10_Click()
Dim X As Single
If OptionButton10 = True Then
If Label6.Caption = "" Then
X = InputBox("PLEASE ENTER DENSITY", vbOKOnly)
Label6.Caption = X
Else
TextBox3.Value = TextBox2.Value * Label6.Caption * 16 + 200 - 25
End If
End If
End Sub
Private Sub OptionButton11_Click()
Dim X As Single
If OptionButton11 = True Then
If Label6.Caption = "" Then
X = InputBox("PLEASE ENTER DENSITY", vbOKOnly)
Label6.Caption = X
Else
TextBox3.Value = TextBox2.Value * Label6.Caption * 24 + 200 - 25
End If
End If
End Sub
Private Sub OptionButton12_Click()
Dim X As Single
If OptionButton12 = True Then
If Label6.Caption = "" Then
X = InputBox("PLEASE ENTER DENSITY", vbOKOnly)
Label6.Caption = X
Else
TextBox3.Value = TextBox2.Value * Label6.Caption * 12 + 200 - 25
End If
End If
End Sub
Private Sub OptionButton13_Click()
If OptionButton13 = True Then
    With Spreadsheet1
        .Range("A2").Value = Label1.Caption
        .Range("B2").Value = TextBox1.Value
        .Range("D2").Value = TextBox3.Value
    End With
End If
End Sub
Private Sub OptionButton14_Click()
If OptionButton14 = True Then
    With Spreadsheet1
        .Range("A2").Value = Label7.Caption
        .Range("B2").Value = Label9.Caption
        .Range("D2").Value = TextBox3.Value
    End With
End If
End Sub
Private Sub OptionButton2_Click()
If OptionButton2 = True Then
TextBox3.Value = TextBox2.Value * 170 + 200
End If
End Sub
Private Sub OptionButton3_Click()
Dim X As Single
If OptionButton3 = True Then
If Label6.Caption = "" Then
X = InputBox("PLEASE ENTER DENSITY", vbOKOnly)
Label6.Caption = X
Else
TextBox3.Value = TextBox2.Value * Label6.Caption * 25 + 200 - 25
End If
End If
End Sub
Private Sub OptionButton4_Click()
Dim X As Single
If OptionButton4 = True Then
If Label6.Caption = "" Then
X = InputBox("PLEASE ENTER DENSITY", vbOKOnly)
Label6.Caption = X
Else
TextBox3.Value = TextBox2.Value * Label6.Caption * 20 + 200 - 25
End If
End If
End Sub
Private Sub OptionButton5_Click()
Dim X As Single
If OptionButton5 = True Then
If Label6.Caption = "" Then
X = InputBox("PLEASE ENTER DENSITY", vbOKOnly)
Label6.Caption = X
Else
TextBox3.Value = TextBox2.Value * Label6.Caption * 18 + 200 - 25
End If
End If
End Sub
Private Sub OptionButton6_Click()
Dim X As Single
If OptionButton6 = True Then
If Label6.Caption = "" Then
X = InputBox("PLEASE ENTER DENSITY", vbOKOnly)
Label6.Caption = X
Else
TextBox3.Value = TextBox2.Value * Label6.Caption * 15 + 200 - 25
End If
End If
End Sub
Private Sub OptionButton7_Click()
Dim X As Single
If OptionButton7 = True Then
If Label6.Caption = "" Then
X = InputBox("PLEASE ENTER DENSITY", vbOKOnly)
Label6.Caption = X
Else
TextBox3.Value = TextBox2.Value * Label6.Caption * 30 + 200 - 25
End If
End If
End Sub
Private Sub OptionButton8_Click()
Dim X As Single
If OptionButton8 = True Then
If Label6.Caption = "" Then
X = InputBox("PLEASE ENTER DENSITY", vbOKOnly)
Label6.Caption = X
Else
TextBox3.Value = TextBox2.Value * Label6.Caption * 20 + 200 - 25
End If
End If
End Sub
Private Sub OptionButton9_Click()
Dim X As Single
If OptionButton9 = True Then
If Label6.Caption = "" Then
X = InputBox("PLEASE ENTER DENSITY", vbOKOnly)
Label6.Caption = X
Else
TextBox3.Value = TextBox2.Value * Label6.Caption * 24 + 200 - 25
End If
End If
End Sub
Private Sub TextBox1_Change()
ScreenUpdating = False
Dim ProdFound As Range
Dim X As Single
    With Worksheets("PRODUCT INDEX").Range("B:B")
        Set ProdFound = .Find(TextBox1.Value)
        If ProdFound Is Nothing Then
            MsgBox ("ITEM NOT FOUND!")
            TextBox1.Value = ""
            Exit Sub
        Else
            With Range(ProdFound.Address)
            Label1 = .Offset(0, 1)
            Label2 = .Offset(0, 3)
            Label3 = .Offset(0, 2)
            Label4 = .Offset(0, 8)
            Label5 = .Offset(0, -1)
            Label6 = .Offset(0, 9)
            TextBox4 = .Offset(0, 9)
            Label7 = .Offset(0, 5)
            Label8 = .Offset(0, 7)
            Label9 = .Offset(0, 4)
            Label10 = .Offset(0, 6)
            Label11 = .Offset(0, 10)
            Label12 = .Offset(0, 12)
            Label13 = .Offset(0, 14)
            Label14 = .Offset(0, 16)
            Label15 = .Offset(0, 18)
            Label16 = .Offset(0, 20)
            Label17 = .Offset(0, 22)
            Label18 = .Offset(0, 24)
            Label19 = .Offset(0, 26)
            Label20 = .Offset(0, 28)
            Label21 = .Offset(0, 11)
            Label22 = .Offset(0, 13)
            Label23 = .Offset(0, 15)
            Label24 = .Offset(0, 17)
            Label25 = .Offset(0, 19)
            Label26 = .Offset(0, 21)
            Label27 = .Offset(0, 23)
            Label28 = .Offset(0, 25)
            Label29 = .Offset(0, 27)
            Label30 = .Offset(0, 29)
            Label31 = .Offset(0, 31)
            Label32 = .Offset(0, 31)
            Label33 = .Offset(0, 25)
            Label34 = .Offset(0, 26)
            Label35 = .Offset(0, 27)
            Label36 = .Offset(0, 28)
            Label37 = .Offset(0, 29)
            Label38 = .Offset(0, 30)
            Label49 = .Offset(0, 31)
            Label40 = .Offset(0, 32)
            Label41 = .Offset(0, 33)
            Label42 = .Offset(0, 34)
            Label43 = .Offset(0, 35)
            Label44 = .Offset(0, 36)
            Label45 = .Offset(0, 37)
            End With
         End If
         If OptionButton1 = True Then
            If Label6.Caption = "" Then
                X = InputBox("PLEASE ENTER DENSITY", vbOKOnly)
                Label6.Caption = X
                Else
                TextBox3.Value = TextBox2.Value * Label6.Caption * 208 + 200 - 25
            End If
        End If
    End With
    ScreenUpdating = True
End Sub
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
We'll take that redundant code and make one sub out of it with its own variable, feed that variable it with the CommandButtons...reduces each one of those to one line of code.
Rich (BB code): 
Option Explicit

Private Sub DensityCheck(MyVal As Long)
Dim X As Single
    If Label6.Caption = "" Then
        X = InputBox("PLEASE ENTER DENSITY", vbOKOnly)
        Label6.Caption = X
    Else
        TextBox3.Value = TextBox2.Value * Label6.Caption * MyVal + 200 - 25
    End If
End Sub

Private Sub OptionButton2_Click()
    If OptionButton2 Then TextBox3.Value = TextBox2.Value * 170 + 200
End Sub

Private Sub OptionButton3_Click()
    If OptionButton3 Then Call DensityCheck(25)
End Sub

Private Sub OptionButton4_Click()
    If OptionButton4 Then Call DensityCheck(20)
End Sub

Private Sub OptionButton5_Click()
    If OptionButton5 Then Call DensityCheck(18)
End Sub

Private Sub OptionButton6_Click()
    If OptionButton6 Then Call DensityCheck(15)
End Sub

Private Sub OptionButton7_Click()
    If OptionButton7 Then Call DensityCheck(30)
End Sub

Private Sub OptionButton8_Click()
    If OptionButton8 Then Call DensityCheck(20)
End Sub

Private Sub OptionButton9_Click()
    If OptionButton9 Then Call DensityCheck(24)
End Sub

Private Sub OptionButton10_Click()
    If OptionButton10 Then Call DensityCheck(16)
End Sub

Private Sub OptionButton11_Click()
    If OptionButton11 Then Call DensityCheck(24)
End Sub

Private Sub OptionButton12_Click()
    If OptionButton12 Then Call DensityCheck(12)
End Sub

Private Sub OptionButton13_Click()
If OptionButton13 Then
    With Spreadsheet1
        .Range("A2").Value = Label1.Caption
        .Range("B2").Value = TextBox1.Value
        .Range("D2").Value = TextBox3.Value
    End With
End If
End Sub

Private Sub OptionButton14_Click()
If OptionButton14 Then
    With Spreadsheet1
        .Range("A2").Value = Label7.Caption
        .Range("B2").Value = Label9.Caption
        .Range("D2").Value = TextBox3.Value
    End With
End If
End Sub

Private Sub TextBox1_Change()
Dim ProdFound As Range
Application.ScreenUpdating = False
    
    With Worksheets("PRODUCT INDEX").Range("B:B")
        Set ProdFound = .Find(TextBox1.Value)
        If ProdFound Is Nothing Then
            MsgBox ("ITEM NOT FOUND!")
            TextBox1.Value = ""
            Exit Sub
        Else
            With Range(ProdFound.Address)
                Label1 = .Offset(0, 1)
                Label2 = .Offset(0, 3)
                Label3 = .Offset(0, 2)
                Label4 = .Offset(0, 8)
                Label5 = .Offset(0, -1)
                Label6 = .Offset(0, 9)
                TextBox4 = .Offset(0, 9)
                Label7 = .Offset(0, 5)
                Label8 = .Offset(0, 7)
                Label9 = .Offset(0, 4)
                Label10 = .Offset(0, 6)
                Label11 = .Offset(0, 10)
                Label12 = .Offset(0, 12)
                Label13 = .Offset(0, 14)
                Label14 = .Offset(0, 16)
                Label15 = .Offset(0, 18)
                Label16 = .Offset(0, 20)
                Label17 = .Offset(0, 22)
                Label18 = .Offset(0, 24)
                Label19 = .Offset(0, 26)
                Label20 = .Offset(0, 28)
                Label21 = .Offset(0, 11)
                Label22 = .Offset(0, 13)
                Label23 = .Offset(0, 15)
                Label24 = .Offset(0, 17)
                Label25 = .Offset(0, 19)
                Label26 = .Offset(0, 21)
                Label27 = .Offset(0, 23)
                Label28 = .Offset(0, 25)
                Label29 = .Offset(0, 27)
                Label30 = .Offset(0, 29)
                Label31 = .Offset(0, 31)
                Label32 = .Offset(0, 31)
                Label33 = .Offset(0, 25)
                Label34 = .Offset(0, 26)
                Label35 = .Offset(0, 27)
                Label36 = .Offset(0, 28)
                Label37 = .Offset(0, 29)
                Label38 = .Offset(0, 30)
                Label49 = .Offset(0, 31)
                Label40 = .Offset(0, 32)
                Label41 = .Offset(0, 33)
                Label42 = .Offset(0, 34)
                Label43 = .Offset(0, 35)
                Label44 = .Offset(0, 36)
                Label45 = .Offset(0, 37)
            End With
         End If
         
         If OptionButton1 = True Then Call DensityCheck(208)
    
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi jbeaucaire,

Thanks! It does shorten the code and is working just fine. I am trying to re-modify it again for some functions I want to happen. Can I give you a shout if I am stuck?

HYKE
 
Upvote 0
HYKE

Perhaps you could shorten the code by cutting down on the number of controls you have on the userform?:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Macro that won't run Trying to use DoWhile Loop and Offset to compare cells
Replies
8
Views
222
Micron
M
P
  • Question
TextBox to count values from ComboBox item
Replies
1
Views
155
Patriot2879
P
C
  • Question
A Bookmark to return to the same row when exiting and opening the application
Replies
8
Views
179
Edgar_
Edgar_
P
  • Question
Email Me. to label and text on two lines issues
Replies
1
Views
175
Micron
M
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
419
Legacy 352679
L

Forum statistics

Threads
1,215,219
Messages
6,123,690
Members
449,117
Latest member
Aaagu

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