Userform and Worksheets
 

Bear with me, since I am slowly teaching myself elementary principles of VBA. I created a userform which utilizes checkboxes to indicate on which worksheets user-input info will be saved. The checkbox captions match the names of the worksheets, and they're set in a frame named Classes. I wrote the code below (my first programming code ever!), and I 'believe' I'm on the right track, but I'm stuck on how to actually save the user-input into the correct worksheets. (More than one checkbox may be selected).

Dim myControl As Control
Dim myWs As Worksheet
Set myWs = ActiveWorkbook.Worksheets
For Each myControl In grpClasses.Controls
If myControl = True Then
If myControl.Caption = myWs.Name Then
Range("H7").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(0, 1).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtAssignment.Value
ActiveCell.Offset(1, 0) = txtPoints.Value
ActiveCell.Offset(2, 0) = txtDate.Value
End If
End If
Next myControl

Thanks for your help!

Userform and Worksheets
 

Sub testit()
Dim ctl As Control, rng As Range

For Each ctl In grpClasses.Controls
If ctl.Value = True Then
On Error GoTo e
With Worksheets(ctl.Caption)
Set rng = .Range("H7")
If Not IsEmpty(rng) Then Set rng = .Cells(7,
Columns.Count).End(xlToLeft).Offset(0, 1)
With rng
.Value = txtAssignment.Value
.Offset(1, 0).Value = txtPoints.Value
.Offset(2, 0).Value = txtDate.Value
End With
End With
e: End If
Next

End Sub


"Robbyn" wrote in message
...
Bear with me, since I am slowly teaching myself elementary principles of
VBA. I created a userform which utilizes checkboxes to indicate on which
worksheets user-input info will be saved. The checkbox captions match the
names of the worksheets, and they're set in a frame named Classes. I wrote
the code below (my first programming code ever!), and I 'believe' I'm on the
right track, but I'm stuck on how to actually save the user-input into the
correct worksheets. (More than one checkbox may be selected).

Dim myControl As Control
Dim myWs As Worksheet
Set myWs = ActiveWorkbook.Worksheets
For Each myControl In grpClasses.Controls
If myControl = True Then
If myControl.Caption = myWs.Name Then
Range("H7").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(0, 1).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtAssignment.Value
ActiveCell.Offset(1, 0) = txtPoints.Value
ActiveCell.Offset(2, 0) = txtDate.Value
End If
End If
Next myControl

Thanks for your help!

Userform and Worksheets
 

Robbyn,

Dim myControl As Control
For Each myControl In grpClasses.Controls
If myControl.Value = True Then
Worksheets(myControl.Name).Range("H7").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Value = txtAssignment.Value
ActiveCell.Offset(1, 0) = txtPoints.Value
ActiveCell.Offset(2, 0) = txtDate.Value
EndIf
Next myControl

HTH
Henry


"Robbyn" wrote in message
...
Bear with me, since I am slowly teaching myself elementary principles of
VBA. I created a userform which utilizes checkboxes to indicate on which
worksheets user-input info will be saved. The checkbox captions match the
names of the worksheets, and they're set in a frame named Classes. I wrote
the code below (my first programming code ever!), and I 'believe' I'm on the
right track, but I'm stuck on how to actually save the user-input into the
correct worksheets. (More than one checkbox may be selected).

Dim myControl As Control
Dim myWs As Worksheet
Set myWs = ActiveWorkbook.Worksheets
For Each myControl In grpClasses.Controls
If myControl = True Then
If myControl.Caption = myWs.Name Then
Range("H7").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(0, 1).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtAssignment.Value
ActiveCell.Offset(1, 0) = txtPoints.Value
ActiveCell.Offset(2, 0) = txtDate.Value
End If
End If
Next myControl

Thanks for your help!

Userform and Worksheets
 

Ok. Third attempt at a reply. Thank you both. I'm using Rob's, but both work :) /danc

Happy New Year!