Calendar control to pick date

[MUSASHI]

Hello!

back on my timesheet project, what I would like is a way to pick the date from a calendar control. This weekly workbook (timebook)will have multiple sheets, a sheet for each day of the week and for each job. So if we work on 4 jobs Monday, there will be 4 timesheets, if we work on 6 jobs Saturday, there will be 6 sheets so on and so forth..(next question will be how to sum all sheets by job, by the way)

So maybe a calendar control in a workbook module? that can be called from a userform on any active worksheet?

any help is appreciated!

Mus

[MUSASHI]

well I can figure out the rudimentary control of cell c5 on sheet from the calendar control

Private Sub Calendar1_Click()
Sheet1.Range("C5").Value = Calendar1.Value
End Sub

2 issues, I want the userform, in this case userform2 to open when cell c5 is selected, and then close. I want the default value to be "", or blank rather. I can just a simple command button to close, but the code to call userform2 seems to be stumping me. Which by the way isnt too difficult

best thing would be when the cell is selected, the userform with calendar pops up, selection is made, then on arrow down, tab, enter key...whatever, to leave C5, the form closes.

thanks
MUSASHI

[Jacob Hilderbrand]

So maybe a calendar control in a workbook module? that can be called from a userform on any active worksheet?

Maybe this is what you would like to do?

http://www.vbaexpress.com/kb/getarticle.php?kb_id=21

[Jacob Hilderbrand]

best thing would be when the cell is selected, the userform with calendar pops up, selection is made, then on arrow down, tab, enter key...whatever, to leave C5, the form closes.

This code will show the User Form when C5 is selected. It goes in the sheet Code Module for the sheet you want this to work on.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("C5")) Is Nothing Then
        Exit Sub
    End If
    UserForm2.Show
End Sub

[MUSASHI]

Heya DRJ,


man o man!! I know it will work, but I get a compile error. I have the same worksheetselection_change sub already in sheet 1. Its the one that MD gave me on my data val thread.

I get a compile error, ambiguous name detected =worksheet_selectionchange.

After that, I been trying for an hour to just put a command button on the sheet to call the darn userform2...

How can I use both in same sheet code module?

Any help is appreciated!
I looked at the article from Dreamboat, man o mna, thats it, however I keep getting funky errors, because of the other option explicit above this one in sheet1

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row() > 12 Then
If Target.Column() = 1 And Target.Row() Mod 2 = 0 Then
UserForm1.Show
End If
End If
End Sub

Option Explicit
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Rng1 As Range
'Assign the range to work with
Set Rng1 = Range("H2")
'Only work on assigned range
If Intersect(Target, Rng1) Is Nothing Then Exit Sub
'Cancel cell editing that would normally trigger when you double click
Cancel = True
'Call the userform
UserForm2.Show
End Sub
'End worksheet module code

these 2 things dont like each other...is there another way?


OMG... , I took out the 2nd option explicit declaration...works great!!!!!

[Jacob Hilderbrand]

So you have it working fine now right?

[MUSASHI]

yeppers, SOLVED!!! huurah for noobs!!!

thanks! Ill get the etiquette down soon!

[Jacob Hilderbrand]

You're Welcome

Take Care