pingwin77
03-04-2009, 08:16 AM
I have a few questions about the following code.
1. How do I convert the values in the Text boxes into a number? (area marked in blue text) If there is nothing in some of the boxes, which can happen, I get an error.
2. How do I set the "Featurevalue=" (marked in red text)to a value I can use when I click a second button in the workbook? I need to copy to sets of data and paste the "Featurevalue=" number of times with different sets of data in my workbook. I don't want the opperators to have to enter the number a second time incase they enter the wrong value.
Thanks in advance to anyone taking a look at this!
Private Sub CommandButton1_Click()
' Add_Rows_Devaiation Macro
' Macro recorded 9/9/2002 by Gary Kapsner
' Macro edited 10/24/2007 by Mike Hemm
' Macro edited 01/06/2009 by Mike Shadick
Application.ScreenUpdating = False
Dim Check, Counter, Features As Range, Message, Title, Default, FeaturesValue
Dim MLRValue
TOTAL = TextBox1
MMC = (TextBox2 * 5)
RFS = (TextBox3 * 3)
PROFILE = (TextBox4 * 2)
ADDITIONAL = TextBox5
FeaturesValue = (TOTAL + MMC + RFS + PROFILE + ADDITIONAL)
Select Case FeaturesValue
Case 0: GoTo line3
Case 1: GoTo line1
Case 2: GoTo Line4
Case Is > 2: GoTo line2
End Select
line1:
Rows(13).Delete
Range("B12").Select
Unload Me
line2:
FeaturesValue = FeaturesValue - 2
Check = True: Counter = FeaturesValue ' Add number of features
ActiveSheet.Rows(13).Select
Selection.Copy
Do ' Outer loop.
Do While Counter > 0 ' Inner loop.
Counter = Counter - 1 ' Increment Counter.
ActiveCell.Offset(1, 0).Activate
ActiveSheet.Paste
If Counter = 0 Then ' If condition is True.
Check = False ' Set value of flag to False.
Exit Do ' Exit inner loop.
End If
Loop
Loop Until Check = False ' Exit outer loop immediately.
Application.CutCopyMode = False
Range("B12").Select
GoTo Line4
line3:
MsgBox "You can not have 0 features in this report. Please try again.", vbOKOnly
Unload Me
Line4:
Unload Me
End Sub
1. How do I convert the values in the Text boxes into a number? (area marked in blue text) If there is nothing in some of the boxes, which can happen, I get an error.
2. How do I set the "Featurevalue=" (marked in red text)to a value I can use when I click a second button in the workbook? I need to copy to sets of data and paste the "Featurevalue=" number of times with different sets of data in my workbook. I don't want the opperators to have to enter the number a second time incase they enter the wrong value.
Thanks in advance to anyone taking a look at this!
Private Sub CommandButton1_Click()
' Add_Rows_Devaiation Macro
' Macro recorded 9/9/2002 by Gary Kapsner
' Macro edited 10/24/2007 by Mike Hemm
' Macro edited 01/06/2009 by Mike Shadick
Application.ScreenUpdating = False
Dim Check, Counter, Features As Range, Message, Title, Default, FeaturesValue
Dim MLRValue
TOTAL = TextBox1
MMC = (TextBox2 * 5)
RFS = (TextBox3 * 3)
PROFILE = (TextBox4 * 2)
ADDITIONAL = TextBox5
FeaturesValue = (TOTAL + MMC + RFS + PROFILE + ADDITIONAL)
Select Case FeaturesValue
Case 0: GoTo line3
Case 1: GoTo line1
Case 2: GoTo Line4
Case Is > 2: GoTo line2
End Select
line1:
Rows(13).Delete
Range("B12").Select
Unload Me
line2:
FeaturesValue = FeaturesValue - 2
Check = True: Counter = FeaturesValue ' Add number of features
ActiveSheet.Rows(13).Select
Selection.Copy
Do ' Outer loop.
Do While Counter > 0 ' Inner loop.
Counter = Counter - 1 ' Increment Counter.
ActiveCell.Offset(1, 0).Activate
ActiveSheet.Paste
If Counter = 0 Then ' If condition is True.
Check = False ' Set value of flag to False.
Exit Do ' Exit inner loop.
End If
Loop
Loop Until Check = False ' Exit outer loop immediately.
Application.CutCopyMode = False
Range("B12").Select
GoTo Line4
line3:
MsgBox "You can not have 0 features in this report. Please try again.", vbOKOnly
Unload Me
Line4:
Unload Me
End Sub