Userform and Calendar

[timarcarze]

Hi Guys,

I have created a userform that includes a calendar pop-up that assists with populating dates, however I continually receive "Run-time error - Application defined or object-defined error", specifically:

Please Login or Register  to view this content.

I cannot break the error, could anyone please assist? Refer attached worksheet.

Cheers

[davesexcel]

If you want to set a value to a control in your userform when it shows, it should go into the userform initialize.

[timarcarze]

Hi davesexcel,

Thanks for responding. Apologies for my novice response, but I don't quite understand what you mean.

I have since tried the full names of the headings (in brackets), e.g.

Please Login or Register  to view this content.

Still no luck with solving the error msg?

[unley]

timarcarze,

If you go to

Please Login or Register  to view this content.

You'll see details regarding userform initialize about half way down the page

[davesexcel]

I don't get what you mean either....
Your orginal code shows you are trying to set a control in the userform before there is a userform, and therefore an error occurs.
You have now displayed a different code.
What's up?

[timarcarze]

I don't get what you mean either....
Your orginal code shows you are trying to set a control in the userform before there is a userform, and therefore an error occurs.
You have now displayed a different code.
What's up?

Sorry, based on your comments I went to the Userform_Initialize CODE and modified some code, but I don't quite understand what I need to do within the Userform_Initialize code to resolve the error msg.

Simply, I would like to show the userform on click of the cmd button, but don't see where I went wrong in the code???

[Leith Ross]

Hello timarecraze,

The problem is with your UserForm_Initialize() code. Change to to what is below and it will run.

Please Login or Register  to view this content.

[timarcarze]

Thanks Leith.
It worked once I removed

Please Login or Register  to view this content.

from the "FillActiveClick" Module, however, it now has the calendar displaying before the userform opens. I'm trying to fix it so the userform displays first and the calendar only pops open when select the date field, but having issues.... could you suggest how to do this?

[Leith Ross]

Hello timarcraze,

The problem is when the txtDate control event code is fired. Currently, it is fired on the Enter event. Since this control is the first control to receive the focus when the UserForm is displayed, the Enter event fires bringing up the the calendar.

Delete the txtDate_Enter() event code and add the code below to the UserForm. The user can enter a date or if they tab, click, or press the enter key with no date, the calendar will be displayed.

Please Login or Register  to view this content.

[timarcarze]

Ah-huh, starting to understand that much better now, thanks.
Is there a function to enable the calendar to 'pop-up' on "clicking" the Date field? For example, when the userform is clicked open, but the user desides to "close" the form (did not need to use), the calendar still pops up once you click "close" and is unecessary.
Thanks
timarcarze

[Leith Ross]

Hello timarcraze,

The textBox on a Userform has no Click event. To get around the problem of the calendar showing up inappropriately, I added the following code to the USserForm_Activate event procedure. This sets the focus to another text box when the UserForm is displayed. Since the txtDate control is the first one in the tab order, VBA will force the cursor back to this text box despite the code action. This will trigger the date to update with today's date. Once a date is in pace, the calendar will not show when the UserForm is closed.

Please Login or Register  to view this content.

[timarcarze]

Hi Leith,

Thanks for your help. I have two other queries I was hoping you could help me with:

(1) I swapped around the txtDate field in the userform to display second, but the calendar pop-up would still display first? (in attempt to have the pop-up display 'on click' within the field)

I was also hoping to "copy and paste" the pop-up calendar code to use within other fields, such as "txtDelRecDate" and "txtBMCDespDate". Could you suggest how I can best do this when the cursor enters each of those text boxes?

(2) I would also like to create a seperate userform that contains a combo box, that enables the user to select from the rows of data entered in the database and subsequently populate the remaining fields (that had not been completed) associated with that row and update the database worksheet accordingly on "submit revised entry". I hope this makes sense. Would you know what code supports this?

Cheers
Timarcarze

[royUK]

You can give a control a value before initialising.

[timarcarze]

You can give a control a value before initialising.

Hi royUK,

Thanks for the advice. I used the previous advice and modified the Activate to show a different field first, however the calendar still pops up immediately upon opening form?

Appreciate if you could provide some advice on my previous post, as I am stuck.

Thanks
T

[timarcarze]

Wasn't getting the right solution on the existing Calendar module, so have found an alternate "Date Picker" that I have inserted into the userform.

The "Tx Refurb Database" worksheet now includes a new column A with the sequential numbering of each row entered into the database.

As per my previous post (qn below) I would like the user to select a drop down of the sequential numbers available and prepopulate the userform fields with the data entered as per database but enable them to complete the fields not entered in the database. On submission of userform, it will continue to update the rows in the database accordingly.

create a seperate userform that contains a combo box, that enables the user to select from the rows of data entered in the database and subsequently populate the remaining fields (that had not been completed) associated with that row and update the database worksheet accordingly on "submit revised entry". I hope this makes sense. Would you know what code supports this?

A seperate Command button, might be easier, e.g. "Update existing entry".

Can anyone please help?

[Leith Ross]

Hello Tim,

Since the workbook will be shared, it would be easier to control which TextBoxes are filled by using the logged on user ID. Which users will be "PAL" and which will "BMC"?

[timarcarze]

Hi Leith,

The fields are indicated with a red * and blue * n the userform respectively for PAL and Blue. However, BMC could still complete the red fields if they were not completed by PAL.

Does this still work with a seperate Cmd button for BMC, e.g. "Update existing entry" and select number from dropdown?

[Leith Ross]

Hello Tim,

By checking the logged on UserID, data can be entered into the correct field. For example, user "A" logs on an opens the workbook. The code can check if user "A" belongs to the authorized "PAL" users or the authorized "BMC" users. Only the fields for that user will then be enabled. So a "PAL" user won't be able to enter data into fileds for a "BMC" user and vice versa.

To make this work, a list needs to made of users and which authorized group they belong to. So I can make a list of IDs for the "PAL" group and the "BMC" group. You can change these to match the real users.

[timarcarze]

Hi Leith,

Sounds like a good idea. Appreciate if you could suggest how this works as I'm unfamiliar with that technique. Please use generic IDs / names.

Basically PAL enters the new entries and BMC updates existing entries entered and updates the remaining fields uncomplete (by PAL).

Selecting the sequential number from the Column A would still play for BMC updating existing entries? right?

Thanks for your help