jtsou
02-05-2015, 10:45 AM
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!
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!