?
Solved

Pop pop calender when clicking textbox in userform

Posted on 2009-12-19
15
Medium Priority
?
596 Views
Last Modified: 2012-05-08
Hi Experts,

I tried to activate Calender in Userform by clicking textbox and update the date in cell, but its not working. Please help me how to do this. Attached the print screen and the excel file.
calendar.png
CMR-Activity.xls
0
Comment
Question by:Theva
  • 9
  • 5
15 Comments
 
LVL 2

Expert Comment

by:kaijaibe
ID: 26086375
I think that you need to change the way you are approaching this.

This snippet in UserForm1 will trigger the calendar to open when the user clicks in the field but it is not correct.  The calendar opens first on initial load.  There needs to be a control that stops the calendar opening on initial load.  Also it should be the calendar that places the date in UserForm1 and not as you currently have UserForm1 trying to take the value from the calendar.
Private Sub TextBox1_Enter()
    UserForm2.Show
End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:patrickab
ID: 26086484
Theva,

Make it so that as soon as the date TextBox1 is entered the Calendar pops up. A doubleclick in the Calendar then enters the date into TextBox1 and closes the Calendar automatically. Re-entering TextBox1 will then make the Calendar popup again.

It's in the attached file.

By the way it is good practice to always use Option Explicit at the top of every code pane. That obliges you to define all the variables - and that is always a good thing to do.

Incidentally the Calendar is not populating itself with dates - it may be something to do with my set up or it might be due to incorrect references to it in your VBA project. However it does return dates when doubleclicked.

Patrick
In the code pane for Userform1:

Private Sub UserForm_Initialize()
Me.TextBox1.SetFocus
End Sub

Private Sub TextBox1_Enter()
UserForm2.Show
End Sub

In the code pane for Userform2:

Private Sub Calendar1_DblClick()
UserForm1.TextBox1 = Calendar1.Value
Unload Me
End Sub

Open in new window

CMR-Activity-01.xls
0
 

Author Comment

by:Theva
ID: 26086510
Hi patrickab,

Any chance to transfer the date into CMR DataSheet(Sheet2), in cell "A3"

I'm tried few codes that was given in Expert Solution for Option Button in Frame but non of this working, perhaps i can say I don't know which one is suitable. Some code managed to trigger but doesn't update my CMR DataSheet accordingly. Please guide me and I'll follow same method for other  Option Button.

Your help is extremely needed.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 45

Expert Comment

by:patrickab
ID: 26086520
Theva,

>Any chance to transfer the date into CMR DataSheet(Sheet2), in cell "A3"

That should be possible, but you need to tell me what triggers that event. If you want it to happen when a button is pressed then let me know which button. If you want it to happen as soon as the date is entered into TextBox1 then let me know that.

Over to you.

Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26086571
Theva,

I am waiting for your response.

Patrick
0
 

Author Comment

by:Theva
ID: 26086601
Hi patrickab,

First, need to trigger "add new activity" button, after entering all information, click "save" button, the entered info should transfer into CMR DataSheet(Sheet2). New entry should target new rows.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26086660
Thanks - I'll be back...
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26086698
Theva,

The code below is in the attached file. You will need to complete the task as described in the comments. Do please ask if I've not been clear.

Patrick
Private Sub Label11_Click()
'Save button
With Sheets("CMR DataSheet")
    'enter the data in the first free row - works up from the bottom of the worksheet
    'use the same approach for all the other entries but use column A as the reference point
    'that will then ensure that all the data is on the same row.
    .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = Me.TextBox1.Value
    'so for column B data the VBA needs to be like the next line - for example
    '.Cells(.Rows.Count, "A").End(xlUp).Offset(0, 1) = Me.TextBox2.Value
    'take careful note of the Offset(0,1) that offsets it zero rows from the first
    'entry in column A but one column to the right - column B. For column C it would be
    '.Cells(.Rows.Count, "A").End(xlUp).Offset(0, 2) = Me.TextBox3.Value
    'and so on
    
    'this greys out the Save button to prevent double entries
    Me.Label11.Enabled = False
End With
End Sub

Open in new window

CMR-Activity-02.xls
0
 

Author Comment

by:Theva
ID: 26086824
Hi Patrickab,

Thanks for the guide. I need your help on how to create code for OptionButton selection in Frame1, Selecting multiple channel in page1&2, and entering date in combo box. Hope you don't mind to assist me.

One more, shall we have Userform1 pop-up as and when we open the workbook, no need to click the button to load the form. The Calender only pop up when we click the textbox.1

I've attached the excel sheet and please check whether i've followed your instruction or not.
CMR-Activity-02.xls
0
 

Author Comment

by:Theva
ID: 26087075
I noticed I made some mistake in the coding. attached the corrected sheet.
CMR-Activity-02.xls
0
 
LVL 45

Accepted Solution

by:
patrickab earned 2000 total points
ID: 26087293
Theva,

I've got to go out for the next 3 hours.

BTW - the tricky bit are the MultiPage parts of the UserForm.

Patrick
0
 

Author Closing Comment

by:Theva
ID: 31668053
Thanks for your help
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26088929
Theva,

I'm afraid it turned out to be 8 hours. I'll see what I can do to justify the A grade you have awarded even before seeing the final result. Back soon...

Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26089209
Theva,

I have at long last done the tricky part, which was the MultiPage part of UserForm1. I have had to change all the Checkboxes into OptionButtons as CheckBoxes cannot be made mutually exclusive. I have made them mutually exclusive by placing them on each page within/on top of a frame. I have also given them Group names.

In a similar way I have 'grouped' the other option buttons. As a challenge I will leave you to complete the VBA for those buttons to place the selections in the database. Do please ask if you get stuck.

It's all in the attached file. BTW you have a fair bit of cleaning up of the code as you had all sorts of saving code in the wrong macro - cmdAdd__Click(). In my view that macro should be reserved for starting a new entry and clearing the UserForm, not for transferring the selections to the database. The Save button macro should be used for transferring the selections to the database.

I hope that all helps.

Patrick
CMR-Activity-03.xls
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26089224
Theva,

BTW CheckBoxes can be made mutually exclusive but it is not done automatically by the compiler. You would need to write the code for it - and that is tedious as a CheckBox_Change() routine needs to be written for each and every CheckBox. So it can be done but it's not worth the effort if OptionButtons can do the job just as well.

Patrick
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question