Solved: Trouble with format of a textbox in a userform

[Pete634]

Hi,

Can anyone help with a problem I am having with a userform? I'm building a master timesheet that will manipulate holidays etc on employees sheets as well as store & maniplate data on the master... won't go into it as I know what I need to do, but I'm clearly missing some basics (basic knowledge probably!!)

I've created the userform (to select the Employee, dates etc) and initialized it. In the form one of my textboxes is called HStartDay (the day the holiday starts) , another is HStartMth (the month the holiday starts). Under Private Sub UserForm_initialise, the only reference to these two cells is as follows:-

Private Sub UserForm_Initialize()

(other code before the following)

HStartDay.Value = ""
HStartMth.Value = ""

(other code after)

End Sub

When the data is entered on to the form and the user presses an "OK" button then I want to check that

HStartMth is not > 12
HStartDay is not > the maximum number of days in the month of HStartMth

The part code I used under Private Sub HformOK_Click() where this is my button on the holday form is : -

If HStartMth > 12 Then
MsgBox "Invalid Date"
HStartMth.Value = ""
HStartMth.SetFocus
Exit Sub
End If

....This works fine

If HStartDay.Value > Worksheets("data").Cells(4 + HStartMth, 4).Value Then
MsgBox "Invalid Date"
HStartDay.Value = ""
HStartDay.SetFocus
Exit Sub
End If

... This doesn't.

So you know, on Sheet "Data" Cells B5-D16 are populated with row B (1-12), row C (Jan-Dec), row D (max number of days in corresponding month).

I've tried everything I can think of (which isn't a lot!!).

If I try and put HStartDay into a cell on Data and then code to compare the relevant cells this doesn't work either. The cell on the worksheet says the number is formatted as text and if I format the cell and then run the macro it changes the format back to general.

Does it matter that I haven't used DIM anywhere yet?

Does anyone have any ideas? Please help a sad VBA newbie !

Apologies for the long post.

[Bob Phillips]

[vba]
If HStartDay.Value > Application.Vlookup(HStartMth.Value,Worksheets("Data").Range("B516"),3,False) Then
MsgBox "Invalid Date"
HStartDay.Value = ""
HStartDay.SetFocus
Exit Sub
End If
[/vba]

[Pete634]

XLD - thanks, but that doesn't work either.

Tried copy & paste your solution, nothing. Tried typing manually, got to application. and vlookup does not appear on the object list.

[XLGibbs]

Try replacing Application. with Application.WorksheetFunction.

[Pete634]

Nope this doesn't work either.

I've attached the spreadsheet so you can see. Click holiday button to activate.

Seems to be OK at recognising HStartMth as a number provided you don't reference to a cell in a worksheet. Would assume this would apply to HStartDay also given that the properties of the boxes are identical. So either I need to make the text box recognise that I am entering a number, or I am missing something incredibly simple.

I also will need to input the value into a cell later in order to compare it with other values, so at some point I'm gonna need to sort this formatting problem out.

Run it and see what I mean - look at cell C19 on Data, I've added code to show HStartDay value in it, and formatted the cell to Number prior to saving the book and attaching to this post.

Thanks.

[XLGibbs]

I would avoid using the lookup at all. You can do a lot with dates behind the scenes. While I look at your form and such...chew on this

To get the last day of a given month (I will assume the year going forward is the "current year") based on the month in HStartMth
[VBA]
Sub IsDateValid()

m = Val(HStartMth.Value)
d = Val(HStartDay.Value)
y = Year(Now())

If IsDate(m & "/" & d & "/" & y) Then
MsgBox "Ok"
Else
MsgBox "not ok"
End If
End Sub


[/VBA]

Try something like that instead... If the values were 6/31/2007 for example, it would return "not ok"

[Pete634]

This looks better, I'll give it a try.

However, I will still have the problem of the format of the textbox not recognising the entry as a number when transferring it to the spreadsheet.

This will have serious complications for me later. If I could only sort this problem out, then I could move forward with the rest of the build.

All assistance is greatly appreciated. Whilst I'm not new to Excel, I am to VBA, so I am learning as we go.

[Bob Phillips]

Try replacing Application. with Application.WorksheetFunction.

Totally unnecessary.

[Bob Phillips]

To be perfectly honest, I think your application needs a re-design. That layou is not conducive to being a database as you are trying to use it. You would better to hol,d the data in a flat form somewhere else, and have a view into it to get the presentational format that you require. Otherwise you are forever programming against the format.

[Pete634]

XLD - I'm not really too bothered how it looks. This workbook is only to hold relevant data and perform calculations on it. Each employee will have their own (identical) workbook which they will be putting their clocking times on and will also show what holiday they have booked, how much they have left, etc, which will all be updated by the control sheet.

I just need to know how to get round this formatting problem and I'd like to use a form for the many options that will eventually be available (and as they are all based around the holiday scenario hence if I can sort this formatting/value problem out, I can sort the rest out).

[Bob Phillips]

Pete,

I am not commenting on its looks, just the fact that structured in that manner is not conducive to extracting and capturing data. Data is better stored in a flat manner in Excel, and then use views to show parts of that data. The structure of the data will mean that you are always coding ariun it, rather than to it.

I gave you an initial 'solution', which from your initial description should have worked. Having seen the layout, the lookup data was horizontal rather than vertical, and I couldn't understand where the rest of the data was/looked like. So, not surprisingly, my suggestion didn't work, and I am not clear enough in my mind what the data looks like to offer a further suggestion.

[Pete634]

Solved it !

Where I have HStartDay.value, replaced with HStartDay.text

With a bit of tweeking of XLGibbs ISDATE works like a dream. Shame that there are issues with ISDATE due to my locale settings being UK.

[XLGibbs]

Solved it !

Where I have HStartDay.value, replaced with HStartDay.text

With a bit of tweeking of XLGibbs ISDATE works like a dream. Shame that there are issues with ISDATE due to my locale settings being UK.

Out of curiousity--what issues? IsDate is checking the datevalue of the number, which is the same in all locales, I believe (being that each day is counted forward beginning as 0 at 0/0/1900 (or even at 0/0/1904). If the string passed to the function is formatted to your locales settings..not sure what issue there would be.