Visual Basic Check Boxes

[emily2309]

I will explain my spreadsheet first to let you know what I am doing.

I am creating a scheduling program for my company. I want the spreadsheet to feedback to me what shifts are available for overtime depending on who is on holiday. I have set it up to show everyones shifts when they are not on holiday. Everyone's shift has an IF statement, which is linked to another sheet. This IF statement basically says that if the colleague is on holiday, do not put their shift here. This means it will be left blank and i will know that they are on holiday.

Right..
I have created a user form, where i will enter who is on holiday. I have a combo box to select the colleague, and then below that i have check boxes to select which days they are on holiday. What i need help with is how to code the check boxes so that when they are clicked, they send data to the spreadsheet to say which one is clicked. It also needs to link to the colleagues name.
Basically, I am going to need to paste into the spreadsheet "on holiday" for each colleague and each day they are on holiday.

I hope this makes sense!

Thanks

 

[MickG]

Hi, That sound like a lot of "Checkboxes", is that one for each day, each week, or how does it work ??
Mick

 

[emily2309]

There is a checkbox for each day, and then one to select all days

 

[GTO]

...I have created a user form, where i will enter who is on holiday. I have a combo box to select the colleague, and then below that i have check boxes to select which days they are on holiday.


What i need help with is how to code the check boxes so that when they are clicked, they send data to the spreadsheet to say which one is clicked. It also needs to link to the colleagues name.


Basically, I am going to need to paste into the spreadsheet "on holiday" for each colleague and each day they are on holiday.

I hope this makes sense!

Thanks

Would I be correct in assuming:

1) You don't mean each time we tick or untick a checkbox we are doing something, but more at, after making our selections, we press a command button?

2) Upon pressing the command button, we go to a certain row (to match the colleague) and enter "on holday" in the correct day(s) column(s)?

Mark

 

[MickG]

Hi, Why don't you use a "Calendar" Control on you "Userform", then you could select "Start Date" and "End Date", then place those date on your sheet, or whatever you want to do after you've selected the dates.
Mick

 

[emily2309]

Yes, there is a command button, and yes that's what i want. I was thinking of using 'copy to database, but i'm not sure how to select certain columns/rows with it.

 

[emily2309]

What i really want to do is know the code to use which will paste into a certain spreadsheet which checkboxes are clicked.
I've got a table, with all the colleagues names along the top, and then the days down the side. i want the form to paste "on holiday" in the correct places in the table depending who's on holiday..

 

[doofusboy]

You mean something like this?

Private Sub CheckBox1_Click()
    If CheckBox1.Value = True Then
        Range("A1").Value = "On Holiday"
    End If
End Sub
Private Sub CheckBox2_Click()
    If CheckBox2.Value = True Then
        Range("B1").Value = "On Holiday"
    End If
End Sub
Private Sub CheckBox3_Click()
    If CheckBox3.Value = True Then
        Range("C1").Value = "On Holiday"
    End If
End Sub

 

[emily2309]

I think that might work, i'll give it a go. THANKS
The only other thing is, it will change where it's pasted depending on who it is that's on holiday, so could i put

If Checkbox1.Value = "True" and ComboBox1.Value = "Name" Then

and then where to paste it?

 

[emily2309]

One thing is, i wanted this to be put in when the user clicks on the command button on the form, not just when they select the check box?