Excel VBA Code getting rather large... 1

[blounty]

Hey guys,

I have created a function to take information from a form and enter into a sheet, then give message box's based on this information. The code below works fine and i am happy with but i have to do this with another form which has about 20 or more text box's is there an easier way of doing this that will reduce the code?

Private Sub CommandButton1_Click()
Dim Count As Long
Dim Make As String
Dim Model As String
Dim Rental As String
Dim Mvol As String
Dim Mcpp As String
Dim Cvol As String
Dim Ccpp As String

Count = Worksheets("Proposal").Range("M3").Value
Make = "N" & Count
Model = "O" & Count
Rental = "P" & Count
Mvol = "Q" & Count
Mcpp = "R" & Count
Cvol = "S" & Count
Ccpp = "T" & Count

If UserForm5.TextBox1.Value = "" Or UserForm5.TextBox2.Value = "" Or UserForm5.TextBox3.Value = "" Or UserForm5.TextBox4.Value = "" Or UserForm5.TextBox5.Value = "" Or UserForm5.TextBox6.Value = "" Or UserForm5.TextBox7.Value = "" Then

MsgBox "Please Enter Values For All Items", vbExclamation, "ERROR"

Else

Worksheets("Proposal").Range(Make).Value = UserForm5.TextBox1.Value
Worksheets("Proposal").Range(Model).Value = UserForm5.TextBox2.Value
Worksheets("Proposal").Range(Rental).Value = UserForm5.TextBox3.Value
Worksheets("Proposal").Range(Mvol).Value = UserForm5.TextBox4.Value
Worksheets("Proposal").Range(Mcpp).Value = UserForm5.TextBox5.Value
Worksheets("Proposal").Range(Cvol).Value = UserForm5.TextBox6.Value
Worksheets("Proposal").Range(Ccpp).Value = UserForm5.TextBox7.Value



If Worksheets("Proposal").Range("AA2").Value > Worksheets("Proposal").Range("AC2").Value Then
MsgBox "New Device Mix Mono Volume Is Less Than Current Device Mix, Please Add More Devices", vbInformation
End If
If Worksheets("Proposal").Range("AA2").Value <= Worksheets("Proposal").Range("AC2").Value Then
MsgBox "New Device Mix Mono Volume Is Now Sufficient", vbInformation
End If
If Worksheets("Proposal").Range("AB2").Value > Worksheets("Proposal").Range("AD2").Value Then
MsgBox "New Device Mix Colour Volume Is Less Than Current Device Mix, Please Add More Devices", vbInformation
End If
If Worksheets("Proposal").Range("AB2").Value <= Worksheets("Proposal").Range("AD2").Value Then
MsgBox "New Device Mix Colour Volume Is Now Sufficient", vbInformation
End If
If Worksheets("Proposal").Range("AE2").Value <= Worksheets("Proposal").Range("AF2").Value Then
MsgBox "New Device Mix Volume Is Now Sufficient, Please Go To Next Stage", vbInformation
End If


UserForm5.ComboBox1.Text = ""
UserForm5.Frame1.Visible = False
End If

End Sub

Any ideas would be great...

Thanks for looking and merry xmas. :->

 

[jebenson]

Two things:

1) This is the VB .NET forum. For VBA questions, post in the VBA forum at thil link: forum707

2) The amount of code you listed above is really not that much at all.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[djj55]

I have created a sub that I pass the value, worksheet (if not "global") and location, however your code looks straight forward and it would be just as easy to code as you have.
Sub PlaceKi(ByVal Amount As Integer, ByVal osheet As Excel.Worksheet, ByVal myRow As Interger, ByVal myCol As Interger)

In my code I had several layers of "For", "If", "With" to contend with as I was finding matching text and handling other problems.
djj