PDA

View Full Version : [SOLVED] Several If statements inside For Loop



ziadhamati
05-26-2016, 06:50 PM
Hello,
It is my first post here since I am a rookie in VBA

I have a userform with two textboxes.

Textbox1:
if user enters 1, it will make the corresponding "Steel DC" sheet visible
if user enters 2, it will make the main "Steel DC" sheet visible and create an extra copy
if user enters 3, it will make the main "Steel DC" sheet visible and create two extra copies
---> so basically for every X number enter, it will make "Steel DC" visible and create X-1 copies

Textbox2:
If user enters 1 or 2, I want it to make the corresponding "Subs (box-pin)" sheet visible
If user enters 3 or 4, I want it to make the corresponding sheet visible, and create 1 copy
If user enters 5 or 6, I want it to make the corresponding sheet visible, and create 2 copies ...

For textbox1, I am able to write the code and works perfectly fine.
For textbox2, I am kind of stuck and confused. Not sure how to figure out a relation to write the code.


Below is code for textbox1


Sub test()
a = Val(TextBox1.Value) 'Steel DC
b = Val(TextBox2.Value) 'Subs (box-pin)
If a = 1 Then
wkb.Sheets("Steel DC").Visible = True
Unload Me
End If
If a > 1 And a <= 10 Then
For numtimes = 1 To a - 1
wsDC.Visible = a > 0
wsDC.Copy after:=ThisWorkbook.Sheets(Sheets.Count)
Next
ElseIf a > 10 Then
wkb.Sheets("Steel DC").Visible = False
MsgBox "Please Enter A Value for Steel Collars"
End If
End sub




Would appreciate your help:)

Sandler
05-26-2016, 07:04 PM
Welcome, and you are definitely not a newbie. Your code looks great. I am a newbie, so writing the code will be an issue for me. However, can't you implement a divide by 2 and round up function into the sheet count. 3/2 is 1.5, 5/2 is 2.5. I look forward to the more seasoned pros answering this question with code.

SamT
05-27-2016, 06:04 PM
Useful if numcopies is based on more than 2 values or the pattern is not standard.

Select Case CLng(TextBox2.Value)
Case 1, 2: NumCopies = 0 'You don't need this. Numerical variables = 0 until set otherwise
Case 3, 4: NumCopies = 1
Case 5: NumCopies = 2
Case 6 to 99: NumCopies = 3
Case Else: NumCopies = 0

End Select

Alternate method: Use a Listbox with two columns. Set Column 1 width = 0 (hidden). In UserForm_Initialize(), set ListBox2.Column1 list = Array("0", "0", "1", "1", "2", "2") and Listbox2 column2 list = Array("1", "2", "3", "4", "5", "6").
Now
NumCopies = CLng(ListBox2.Value)

Alternate method 2: Dim Module level variable Text2Copies As Collection
Useful if many numbers of Numcopies needed in a fixed pattern

Dim Text2Copies As Collection

Sub UserForm_Initialize()
For i = 0 to 2
For j = 1 to 2
Text2Copies.Add Item(i)
Next
Next

Now

NumCopies = Text2Copies(CLng(TextBox2.value))


Alternate Method 3: very fast

Dim NumCopies2 As Variant
Sub UserForm_Initialize()
NumCopies2 = Array(0, 0, 1, 1, 2, 2

Now
NumCopies = NumCopies2(CLng(TextBox2.Value - 1)

Alternate method 4: Good for many TextBox values but few numbers of NumCopies needed

With TextBox2
If CLng(.Value) < 3 Then: NumCopies = 0
ElseIf CLng(.Value) < 5 Then: NumCopies = 2
ElseIf CLng(.Value) < 7 Then: NumCopies = 3
Else: NumCopies = 4
End If
End With

I can go on. There are at least 99 more methods, including Sandlers.

Note the use of CLng() in every example. That is because all UserForm controls return Strings and CLng converts them to numbers. Use CDbl() for decimal numbers and CDate() for dates