Looping IF

[jtsou]

Hello everyone,

First time here, and I am very new to VBA.

I have an excel spreadsheet that I am trying to play around with.

I know there should be a way to do this but I do not know exactly how.


I have a sheet with 5 columns.

The first column is a number 1-40
Second is a person's name, last name first ordered a-z
Third is a person's job position
Fourth is their ID
Fifth is their alternate ID

I have a form that comes up with everyone's name assigned to a checkbox, I have this working great.

I want the form to delete data for anyone checked.

The code I have now is

Private Sub CommandButton1_Click()

If CheckBox1.Value = True Then
Sheet15.Range("B3").Value = ""
Sheet15.Range("C3").Value = ""
Sheet15.Range("D3").Value = ""
Sheet15.Range("E3").Value = ""
End If




Range("B3").CurrentRegion.Select
Selection.Sort Key1:=Range("B3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Unload Me


End Sub

This looks at checkbox1, and if it is checked it deletes cells B3, C3, D3, and E3. Then resorts column B with the new list, then closes the form.

This works good.

The main question I have is instead of listing out every checkbox(1-40) what would be the syntax for a looping IF statement so I dont have to do that.

For example instead of:



If CheckBox1.Value = True Then
Sheet15.Range("B3").Value = ""
Sheet15.Range("C3").Value = ""
Sheet15.Range("D3").Value = ""
Sheet15.Range("E3").Value = ""
End If

If CheckBox2.Value = True Then
Sheet15.Range("B4").Value = ""
Sheet15.Range("C4").Value = ""
Sheet15.Range("D4").Value = ""
Sheet15.Range("E4").Value = ""
End If

I have tried figuring this out online, with no luck.

Thanks for your help!

[Paul_Hossler]

Assuming the CheckBoxs are CheckBox1 ... CheckBox40


(Not tested very well)



Option Explicit
Private Sub CommandButton1_Click()
Dim i As Long

With Sheet15
For i = 1 To 40
If Me.Controls("CheckBox" & i) Then
.Cells(i + 2, 2).ClearContents
.Cells(i + 2, 3).ClearContents
.Cells(i + 2, 4).ClearContents
.Cells(i + 2, 5).ClearContents
End If
Next I
End With
End Sub



BTW, I'd use .ClearContents since that makes it really empty, whereas = "" makes it into a 0-length string. Looks empty, but not really

[apo]

Hi..

Just a suggestion.. as you want to clear the contents of a contiguous range.. maybe using the Resize property will knock out 3 lines of that code..?