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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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

H
  • Question Question
Passing Value from Sub to Userform textbox
Replies
1
Views
281
NdNoviceHlp
NdNoviceHlp
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
158
krunal123
K
M
  • Question Question
offset Combobox.ListIndex
Replies
1
Views
144
RoryA
RoryA
P
  • Question Question
RUNTIME ERROR 91 for USERFORM CODE
Replies
1
Views
47
6StringJazzer
6StringJazzer
I
  • Solved
Load into textbox two values from customer on worksheet
Replies
6
Views
74
AlphaFrog
AlphaFrog

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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