Getting data off a form onto a worksheet

[RobEdgeler]

Hi. Any help appreciated on this one will be gratefully received. I'm
no expert in this as I'm just an end user with a desire to make things
better but I always try to have a go at solving it all myself, but I
get stuck...easily!

I have a spreadsheet that contains a list of staff names in the various
rows. In the columns, I have tasks that they should have completed as
part of their training. The UserForm allows the clerk to check the
boxes for each appropriate task. I want the check box to be converted
to a value of "X" if True and then for this "X" to be put in the
worksheet in the appropriate column to highlight that the activity has
been completed. There are about 60 of these CheckBox controls (split
over 3 pages on the MultiPage form).

I have this so far:

Dim myBox As Control
Dim myValue As Long

myValue = 0
For Each myBox In Me.MultiPage1.Pages *** ???? Not correct I think.
If TypeOf myBox Is MSForms.CheckBox Then
myValue = myValue + 1
If myBox.Value = True Then
Sheets("Colleague Details").Select
Range("C3").Select
Selection.End(xlDown).Select *** This is the address of the
cell that contains the name of the staff member, always the last one
entered in the list
*** Here I need to be able to calculate the address of this
cell dynamically and offset to the relevant column to enter "X". So
for example, if CheckBox 6 is True, Column F on the worksheet should
have an "X" in the appropriate row as should Column Z if CheckBox 26 is
True. If these are the only two CheckBox controls selected, the other
columns should have nothing in them.
ActiveCell.Value = "X"
Exit For
End If
End If
Next myBox
End Sub

I'd be so grateful if I could get help with this.

Thanks in advance,

Rob.

 

[Ron de Bruin]

Hi RobEdgeler

You can use this for one check box

Private Sub CheckBox1_Click()
If Me.CheckBox1.Value = True Then
Sheets("Sheet1").Range("A1").Value = "X"
Else
Sheets("Sheet1").Range("A1").Value = ""
End Sub

Maybe you can create a macro for all checkboxes
http://www.j-walk.com/ss/excel/tips/tip44.htm

 

[gutch01]

This is strictly my approach but the range that has the data should be
named. From excel that is Insert/Name/define. then I would set up a
combobox with just the name range as the rowsource. in the visualbasic
form mode I would click on the combobox which will bring up the private
sub for you to write code for the click or change event.

The code when a staff member is selected will then be something like
with range("database")
sStaff= combox.text
iRow=1
Count= .rows.count
do while i>count+1
if sStaff=.cells(iRow,1) then

if .cells(iRow,2)=True then
checkBox1=true
else
checkbox1=false
end if

if .cells(iRow,3)=True then
checkBox2=true
else
checkbox2=false
end if

end if

now put in a command button to record the chanages in the check boxes
to the database on the command click event private sub the code would
be similar to the above, but you would change the correct cell to agree
with the corresponding check box. If you want me to write it real
quick without any frills email me the database and I do a quick and
dirty. From that you should be able to expand with some frills or
other information needs.


checkbox2=True if .cells

 

[RobEdgeler]

Hi and many thanks for your response. I can send you my database n
problem, would you like to email me your email address t
(e-mail address removed) and then I can send it to you.

Thanks for taking the time to help me, I appreciate it.

Kind Regards

Rob

 

[gutch01]

My email address is (e-mail address removed)