Date Problems on VBA Forms

[WillieC]

Hey there WillieC,

Presuming you were asking as to disabling another combo/text/similar control based on a certain value (option) being chosen, I was just answering that this is possibly a couple of different ways.

Here's an example that disables the 'AddRemove' box anytime that "Mat Form" is chosen in the 'WhereFrom' box.

Mark


[vba]Private Sub cboWhereFrom_Change()
If cboWhereFrom.Value = "Mat Form" Then
cboAddRemove.Enabled = False
Else
cboAddRemove.Enabled = True
End If
End Sub
[/vba]

Hi Mark,

That's worked perfectly. Thanks very much mate.

Just two other things that I could do with some help with.

1. When more than one user is using the form, if they click on OK and Save at the same time, a conflict error comes up as the form tries to enter both sets of data into the same row. Any ideas how to solve this?

2. When a user opens the Form, I'd like Excel to minimize so that the form will appear on screen and can be used without having to have Excel in the background.

Thanks again for all your help

[GTO]

Greetings and happy to help,

re 1. Nope.

I did mention this to a friend and co-worker, and he may get to take a look-see.

re 2. Bob (xld) already answered this around post 14.

Application.Visible = False

...should be either right before frmWhatever.Show, or you could put it in the form's activate event. Naturally you'll want to ensure that the app's visibility is returned when the userform(s) is/are dismissed. I'd think the query close event a good place where you couldn't forget it under some button or other.

[WillieC]

Cheers GTO. I had forgotten about XLD's code from earlier but that work's a treat. Cheers to XLD also.

Another question, sorry for being a pain, is there a way of restricting the number of characters that a user can input into a textbox?

[Bob Phillips]

This stops more than 5 being entered

[vba]

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If Len(Me.TextBox1.Text) > 4 Then

MsgBox "Too many chars"
KeyAscii = 0
End If
End Sub
[/vba]

[Bob Phillips]

I had forgotten about XLD's code from earlier ...

A lot of water since then :-)

[Aussiebear]

WillieC, I can appreciate the fact that Bob has been helping you in this matter, as I sure you do as well, but this is a public forum. Either post your workbook to the forum or take it offline with Bob.

You were asked to post a workbook to the forum, but sent to Bob privately. How can anybody assist you if this is being dealt with off site?

[GTO]

@Aussiebear: Greetings and Howdy from Arizona. I believe WillieC's latest ver of wb in question is at/about post #32.

Mark

[GTO]

@xld:

Greetings Bob and best to your and yours. For my education, is there an advantage to keypress vs properties?

Thank you so much,

Mark

[Bob Phillips]

What properties are you referring to?

[GTO]

Well... i should probably start feeling abashed now, but I took the Q. as to the/a userform textbox, ie @ maxlength. 'Yeeks!' on me?

[WillieC]

I posted the file to the forum but here it is again, in case it has been removed.

[WillieC]

This stops more than 5 being entered

[vba]

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If Len(Me.TextBox1.Text) > 4 Then

MsgBox "Too many chars"
KeyAscii = 0
End If
End Sub
[/vba]

Thanks XLD. I just realised that I could change the character length in the textbox's properties. D'oh!

[WillieC]

Updated version guys. The problem I'm having now is that when Today's Date is selected, anything entered after that automatically drops to the next row. Is there any way that I can use the pop up calendar to insert today's date but will also allow me to fill in the rest of the same row??

[Bob Phillips]

It doesn't do anything when I click a date. BTW, why ask them to select Today's date?

[Demosthine]

Good Afternoon there.

GTO suggested I take a look at this thread because I've done a fair amount of work with Shared Workbooks. As XLD said, it can cause quite the mess out of your project and it took me a good deal of time ironing out of the problems I had.

So the other information aside (since the others seem to be helping well with that), if you would be so kind, run through the method you are using to allow multiple users to open the workbook at once. That'll help me pin down the particular errors you are having...

Scott

[WillieC]

Good Afternoon there.

GTO suggested I take a look at this thread because I've done a fair amount of work with Shared Workbooks. As XLD said, it can cause quite the mess out of your project and it took me a good deal of time ironing out of the problems I had.

So the other information aside (since the others seem to be helping well with that), if you would be so kind, run through the method you are using to allow multiple users to open the workbook at once. That'll help me pin down the particular errors you are having...

Scott

At the minute I'm just using the shared workbook method which has a macro for the users to open the form.

[WillieC]

It doesn't do anything when I click a date. BTW, why ask them to select Today's date?

I have that in there as I wasn't sure how to automatically select today's date on the form.

[Demosthine]

Good Morning Everyone.

To insert Today's Date automatically, place a command in your UserForm_Initialize or UserForm_Activate Event. The code would look something like:

[VBA]
lblTodayDate.Caption = Format(Now(), "mm/dd/yyyy")
[/VBA]


A few other notes while I'm posting...

When I load the project, I am missing "ffOcx" and I'm not sure what that is. Is it used in the project or is it a miscellaneous extra?

After I load the UserForm frmDataCapture and click on the "Click to select Date of Demand" button, it automatically resets to today's date. I would assume that's not what you want, so you'll want to remove delete the code from Calendar7_OnClick.

Or better yet, write some code in that Event Procedure to validate the date. For example, you can not have a date that is prior to today for your Date of Demand, right?

[VBA]
Private Sub Calendar7_OnClick
Dim datToday as Date

datToday = Format(Now(), "mm/dd/yyyy")

If Calendar7.Value <= datToday Then
MsgBox "You must select a future date."
End If
End Sub
[/VBA]


For practical programming, you have eight forms that are identical in design. Since your two Data Capture Forms each use four of these Forms, I've written a small snippet in the attached Workbook that should simplify your project. By just deleting these eight UserForms, I took the Workbook size from 345KB to 233KB. Those eight forms took up 1/3rd of your Workbook size!

In the example workbook, when you move your Mouse over the Labels with the dates, the Pointer will turn to a hand. Click on the Label and the Calendar will appear at the correct position. When you click the Date on the Calendar, it will disappear.


I am still looking into the Shared Workbook issue for you, but don't have anything substantial yet.

Scott

[WillieC]

Thanks for the help, Demosthine. Excellent work with the Calendar. Just on thing. That calendar example, I'm having some problems with it. Most likely just me being stupid but I can't get the Calendar working. Where do I put it into the form that I have?

Sorry for being a nuisance

[Demosthine]

Good Afternoon again.

You can put the actual Calendar Control anywhere (out of the way) on frmDataCapture and frmDataCapture2. You'll want to replace your four command buttons on each form that say "Click here for date." or whatever it says. Make them labels and format them how you want them to look.

Double click each of the four new labels and substitute the code inside of the Label_Click Events from my sample workbook and the Calendar_Click Event from my sample. You'll have to change the name in each of the events where I had Set ctlActiveControl = lbl*Val.

If you still can't figure it out, repost the workbook witht the changes you've made reference the above and I'll work with you there.

Scott

P.S. Please work on your Control Naming. It is never good practice, especially with a project that seems to be growing ever-larger, to use controls named Calendar1, Calendar8, etc. Use names that mean a little more, like I did with the sample Workbook. lblDateOfDemand and lblTodaysDate, for instance. This greatly simplifies debugging and other people trying to figure out what you're doing. It's hard to go hunt down what Text1 is supposed to be every time.