Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform and Worksheets
Ok. Third attempt at a reply. Thank you both. I'm using Rob's, but both work :) /danc
Happy New Year! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userform to appear on top? | Excel Discussion (Misc queries) | |||
Userform | Excel Discussion (Misc queries) | |||
Userform Help in VBC | Excel Discussion (Misc queries) | |||
UserForm help | Excel Programming | |||
userform | Excel Programming |