Appearance of calendar

[pansovic]

How can I make that when I click on a certain cell that automatically appears the calendar where i than choose a date?

[contaminated]

Select any cell within the range A1:E29 and say if you meant calendar like this????

see file...

[pansovic]

Select any cell within the range A1:E29 and say if you meant calendar like this????

see file...

Yes that is what I want but would be even better withoout the insert button on the calendar. Just when you click on a date than automatically closes the calendar and fill in the date in the cell.

[contaminated]

Do you want to pick a date from calendar or just insert today's date on selection change within certain range???

[pansovic]

Do you want to pick a date from calendar or just insert today's date on selection change within certain range???

I want to choose any date from the calendar. The date will always be in the future from today. It is for a form to fill in.

[contaminated]

Double Click on a desired date....

[pansovic]

Double Click on a desired date....

That look nice but how do I make this myself now?

[Marcol]

Try this one

It will not allow Date entries before todays date. I think you were asking for that.

This example works on a combimation of data validation and additional control in the UserForms

To use with your project

1/. With the attached file Open the VB editor (alt+f11

2/. In the Project Explorer Pane (Ctrl+R if it is not showing). highlight
Userform1 (for a form with a CommandButton)
Userform2 (for a form without a CommandButton)
Then File > Export File......
Save to your chosen folder.
Close the attached file

You only have to do this once for each UserForm.

3/. With your file, in the VB Editor
File > Import File......
Select your chosen folder. select the saved UserForm1 or 2 as required
Press Open

4/. In the Project Explorer Pane
Double Click the Sheet you want the form to work with.
In the blank module that appears
Paste All of this Code

Please Login or Register  to view this content.

In the Sub Worksheet_SelectionChange
Change

Range("A2:A" & Rows.Count))
to the Range or Cells you want the calendar to work with

and

UserForm1.Show
to suit the form you have chosen.

5/. The Data validation is applied with the code and will come into effect if the user changes a cell manually


Hope this helps

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

[pansovic]

Try this one

It will not allow Date entries before todays date. I think you were asking for that.

This example works on a combimation of data validation and additional control in the UserForms

To use with your project

1/. With the attached file Open the VB editor (alt+f11

2/. In the Project Explorer Pane (Ctrl+R if it is not showing). highlight
Userform1 (for a form with a CommandButton)
Userform2 (for a form without a CommandButton)
Then File > Export File......
Save to your chosen folder.
Close the attached file

You only have to do this once for each UserForm.

3/. With your file, in the VB Editor
File > Import File......
Select your chosen folder. select the saved UserForm1 or 2 as required
Press Open

4/. In the Project Explorer Pane
Double Click the Sheet you want the form to work with.
In the blank module that appears
Paste All of this Code

Please Login or Register  to view this content.

In the Sub Worksheet_SelectionChange
Change

Range("A2:A" & Rows.Count))
to the Range or Cells you want the calendar to work with

and

UserForm1.Show
to suit the form you have chosen.

5/. The Data validation is applied with the code and will come into effect if the user changes a cell manually


Hope this helps

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

This is exactly what I was looking for though I still get an error message when I protect the sheet. So when the sheet is unprotected than it works but when I protect the sheet and the cell (where I need the calendar) is unblocked I get error message and in the VBEditor is marked in yellow the following: " .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, _
Operator:=xlGreater, Formula1:="=NOW()+1""

Any clever idea?

[Marcol]

Hi pansovic

Please don't qoute entire posts in your replies, it is not nescessary and only clutters the BB.
If you do need to refer to a post only quote the bit that is essential to clarify your reply.

Try this

Please Login or Register  to view this content.

If you are using a password then change the above lines to

Please Login or Register  to view this content.

That should do the trick

Cheers.

[pansovic]

Now I get error message "Compilation Error - Variable is not defined" Marked in yellow: "Private Sub Worksheet_SelectionChange(ByVal Target As Range)". marked in blue is "UserForm1".

[Marcol]

You have not copied the userforms to your workbook.

I have attached an updated file to this post.

Follow the instructions in Post #8

Cheers

P.S.

If you only use UserForm1 delete this section of code

Please Login or Register  to view this content.

Similarly if you decide to use UserForm2 delete this

Please Login or Register  to view this content.

That should solve the problem

[pansovic]

The calendar that I want to appears is on Cell C34 and E34. Apparently
"Set isect = Intersect(Target, Range("$C$34;$E$34" & Rows.Count))"
is not working

[Marcol]

If you only want the calendar pop up in cells C34 & E34 then use this line in place of your line
"Set isect = Intersect(Target, Range("$C$34;$E$34" & Rows.Count))"

Please Login or Register  to view this content.

[pansovic]

I want to make this SOLVED but how can I get to my origenal post. I search for the threat, can find it but always arrive on this one and all above there is no EDIT botton. This is not the first time that I have trouble with this. Just don't remember how I done it last time. Would be much better if there is a botton to cleck on the last post.
When I click on EDIT botton of last post, than Go Advanced there is no Prefix botton

[teylyn]

marked solved.

[Fattie]

Is there any way to run this and it allows you to choose dates both in the future and in the past?

[royUK]

See the options here
http://excel-it.com/free_addins.htm