Date Problems on VBA Forms

Quote:

---

Originally Posted by GTO

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. :beerchug:

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

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.

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?

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]

Quote:

---

Originally Posted by WillieC

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

---

A lot of water since then :-)

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?

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

Mark

@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

What properties are you referring to?

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

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

Quote:

---

Originally Posted by xld

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!

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??

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

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

Quote:

---

Originally Posted by 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

---

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

Quote:

---

Originally Posted by xld

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.

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

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

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.

Thanks Demosthine, I'll give that a go. Much appreciated

Hi Demosthine.

I've tried to get the calendar control to work but I keep getting an error. Attached is the file with the changes I've made. Thanks for everything so far.

Ok Willie, I'm thoroughly confused. You attached what is supposed to be your file with the changes made, but the only sign I see that you've made any changes is that you've imported the MousePointer Module, which is the least important of the information I provided in the working example.

On frmDataCapture and frmDataCapture2, you are still using CommandButtons instead of Labels. You still have eight different UserForms for your Calendars, and there is no sign of implementing the example's code. Everything is missing, right down to the Module-Level variable for the ActiveControl.


If you'll read any of my posts, you'll find that I don't "do the work for you," but instead provide suggestions in assisting you in completing it. With a project of this size, I'm sure you are not expecting anyone to do it for you. That said, I literally provided code that you can copy and paste into your project. With the proper naming of your four Labels and your Calendar Control, the example is fully functional.

I don't wish to sound harsh, but would ask that you make a better attempt at implementing the code or suggestions that we provided to you. After that, should you still experience an error, feel free to repost with the new error and I will be happy to take a look at it from there.

Scott

Hi Scott,

Sorry, I posted the wrong file. Apologies for that. I'm trying something else for the dates as I've just found out yesterday that this is only going to be used for about 3 months or so and then our IT dept are going to have an all singing, all dancing form.

Thanks so much for all your help. At least I know how helpful you guys are if I need any more assistance.

PS I never expected any of you guys in here to do the work for me. I've been asked to do this and all I wanted was a few hints and tips here and there.

Thanks again

Afternoon Guys,

Everything seems to be fine so far but I've just noticed that when I click on Demand Date and it is entered into the spreadsheet it auto formats to US style dates from the 2nd column onwards. I have formatted the Date column so that it's the UK format but it won't stick. Can you help?