Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data off a form onto a worksheet
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. :) :) :) -- RobEdgeler ------------------------------------------------------------------------ RobEdgeler's Profile: http://www.excelforum.com/member.php...o&userid=27336 View this thread: http://www.excelforum.com/showthread...hreadid=471188 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data off a form onto a worksheet
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 -- Regards Ron de Bruin http://www.rondebruin.nl "RobEdgeler" wrote in message ... 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. :) :) :) -- RobEdgeler ------------------------------------------------------------------------ RobEdgeler's Profile: http://www.excelforum.com/member.php...o&userid=27336 View this thread: http://www.excelforum.com/showthread...hreadid=471188 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data off a form onto a worksheet
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 icount+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 wrote: 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. :) :) :) -- RobEdgeler ------------------------------------------------------------------------ RobEdgeler's Profile: http://www.excelforum.com/member.php...o&userid=27336 View this thread: http://www.excelforum.com/showthread...hreadid=471188 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data off a form onto a worksheet
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 and then I can send it to you. Thanks for taking the time to help me, I appreciate it. Kind Regards Rob -- RobEdgele ----------------------------------------------------------------------- RobEdgeler's Profile: http://www.excelforum.com/member.php...fo&userid=2733 View this thread: http://www.excelforum.com/showthread.php?threadid=47118 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data off a form onto a worksheet
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sending data from a form to another worksheet | Excel Discussion (Misc queries) | |||
use a worksheet as a form to collect data | Excel Worksheet Functions | |||
Data Form used in another worksheet | Excel Discussion (Misc queries) | |||
Using a Worksheet Form to add data to a separate worksheet databas | Excel Worksheet Functions | |||
Input Form on Worksheet 1, Data on Worksheet 2 | Excel Programming |