how to restrict the date format and assign the autonumber automatically for each column?

**jasonlewis** · 06-29-2013, 09:47 AM

how to restrict the date format (e.g dd/mm/yyyy) and assign the autonumber automatically for each column?

Thank for helping!!

**AndyLitch** · 06-29-2013, 01:01 PM

Please Login or Register  to view this content.

I'm not sure how to autonumber a column based on what you've said .. Can you be more explicit

**jasonlewis** · 06-30-2013, 06:09 AM

why I still cannot restrict the date format

My code is
Private Sub textbox_date_change() <--- I wanna input the data in textbox_date
Range("S:S").NumberFormat = "ddd dd mmm yyyy"
End Sub

The Range("S:S") mean that I would like to input the data in S6,S7,S8,S9,etc.... am I right??

I want to automatically assign the item number to each data I have input.
Sorry for my unclear interpretation.

**AndyLitch** · 06-30-2013, 06:13 AM

OK .. now you mention textboxes and incremental row storage, I have a clearer idea

Give me a while and I'll get back to you with a proposed solution.

Please Login or Register  to view this content.

**HaHoBe** · 06-30-2013, 06:31 AM

Hi, jasonlewis,

please add code-tags if you show procedures here in the forum.

I would not recommend to use the change-event but rather the exit-event, and I would restrict the texbox to just accept numbers and one kind of limiter/divider in order to evaluate the date.

The Range("S:S") mean that I would like to input the data in S6,S7,S8,S9,etc.... am I right??

Not really - it will give the number format to the whole column. From what I read you would rather need something like

Please Login or Register  to view this content.

Ciao,
Holger

**jasonlewis** · 06-30-2013, 08:57 AM

Re andy,

whether I use Private Sub CommandButton1_Click() or Private Sub textbox_date_change(),
I can still input the wrong date type (e.g. I have successfully typed 2323232 and the system did not restrict it)

**jasonlewis** · 06-30-2013, 08:59 AM

Re HaHoBe,

Do you means the code should be
Private Sub textbox_date_change()
Range("S") & Rows.Count.End(xlUp).Offset(1, 0) = Format(textbox_date_change.Value, "ddd dd mmm yyyy")
End Sub

If it is correct, the code is still cannot work = =

**HaHoBe** · 06-30-2013, 09:03 AM

Hi, jasonlewis,

maybe I should have a better look at what I type

- it should read

Please Login or Register  to view this content.

Maybe you should indicate how your value inside the textbox will look like.

Please remember to add code-tags around your procedures posted here.

Ciao,
Holger

**jasonlewis** · 06-30-2013, 10:27 AM

Please Login or Register  to view this content.

I have typed these code in my textbox, but it still cannot restrict the date type of the input data.
When I type "32323232", it displays a lot of date with "ddd dd mmm yyyy" format.

**HaHoBe** · 06-30-2013, 10:35 AM

Hi, jasonlewis,

what would you like to type into the texbox and a) how should that look in the textbox and b) how should it look like in the worksheet? I mentioned before that I would not use the change-event as that gets triggered by any change/entry inside the Textbox (which I doubt might be useful as the change of date format should trigger that event as well).

How about using a userform for choosing the date instead of typing the information into a textbox?

Ciao,
Holger

**john456852** · 06-30-2013, 10:43 AM

Please Login or Register  to view this content.

**AndyLitch** · 06-30-2013, 11:32 AM

Jason your original question appears to be evolving

Originally Posted by jasonlewis

how to restrict the date format (e.g dd/mm/yyyy) and assign the autonumber automatically for each column?

Thank for helping!!

**jasonlewis** · 07-01-2013, 10:15 AM

Re HaHoBe,

Here is my sample, maybe it can help you understand easily.
Do you mean using a combo box to replace text box?

**jasonlewis** · 07-01-2013, 10:15 AM

re john, the code is not work.

**jasonlewis** · 07-01-2013, 10:16 AM

Re Andy,

maybe...

**HaHoBe** · 07-01-2013, 12:27 PM

Hi, jasonlewis,

it would have been nice if the workbook had shown a couple of sample data how it should look like as well as the UF oyu have developed, and although I tried very hard I can´t get the *.png into my VBE for testing. Besides: I wanted to know if you enter for example

Formula:

Please Login or Register  to view this content.

into the textbox (or how you want the date entered to look like while entering and for putting into the workbook).

And as a note: I don´t like MergedCells. I can work wioth them but I like it better to simply avoid them.

Ciao,
Holger

**jasonlewis** · 07-02-2013, 02:06 AM

Maybe I update my work here, so that u may know my condition clearly.

the data is just a sample format.

**john456852** · 07-03-2013, 10:25 PM

For cdate(date) date is the wanted date

Also, it is columns(s:s) not range

**jasonlewis** · 07-03-2013, 11:05 PM

Are you giving a suggestion for my code regarding how to restrict the date format?

**jasonlewis** · 07-04-2013, 11:56 PM

Anyone knows how to do it ><

**jasonlewis** · 07-08-2013, 01:55 AM

Do someone know how to assign the autonumber automatically for each column?

**HaHoBe** · 07-08-2013, 03:57 PM

Hi, jasonlewis,

in Post#3 you add code showing a textbox but I can´t find one in any of the workbooks you uploaded. You show a picture of how that form may look like - but having the picture is one thing and seeing this UserForm/form in Excel/VBA is something else because that might show the necessary information to answer your questions.

Ciao,
Holger

**jasonlewis** · 07-08-2013, 10:15 PM

Here is my sample form,
I want the system automatically assign the item no. when I enter any item code each time.

Also I wanna know how to set the value of monthview control to today date. Thanks.

PW:vbapass

**HaHoBe** · 07-09-2013, 12:31 PM

Hi, jasonlewis,

Here is my sample form,

Maybe but somebody has locked the VBA-Project so I at least can´t tell if it´s in there. I get the note that one control (MonthView) isn´t installed on my PC but I can´t help to overcome it. So what I shall I answer on a workbook with a protected sheet with a protected VBA-Project?

I´ve got one on my mind: I won´t let you have part of my protected knowledge.

Holger

**jasonlewis** · 07-09-2013, 10:19 PM

Sorry, I forget to give my pw to u all,
My pw:vbapass

And I can successfully assign the autonumber for each column automatically.
However, I dun know how to set the value of the monthview control to today date
And my fd said that did not know the reason why he enter the date (10/7/2013) into the form, it output to 7/10/2013.

Moreover, he have set the other two reasons (broken and contaminated) to restrict any input into the monthview control box, but he dun know why it still can output the date of expiration.

Thanks.

**HaHoBe** · 07-10-2013, 12:36 AM

Hi, jasonlewis,

code is untested:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Ciao,
Holger

**jasonlewis** · 07-10-2013, 10:57 PM

Thanks.

But can I set the grey oval shape cover the today date only? I dun want to compel the users must select the today date. (JUST LIKE the pics I uploaded)

Also, I dun know why I also have the problem that when I enter the date (11/7/2013) into the form, it output to 7/11/2013.

Moreover, I hv set the other two reasons (broken and contaminated) to restrict any input into the monthview control box, but it still can output the date of expiration.

Thanks,Jason.

123.PNG

**jasonlewis** · 07-13-2013, 07:44 AM

I have solved the problem of 2 and 3,

But do someone know how to set the grey oval shape cover the today date only? I dun want to compel the users must select the today date. (JUST LIKE the pics I uploaded)

Thanks,Jason.

**HaHoBe** · 07-13-2013, 05:17 PM

Hi, jasonlewis,

I have solved the problem of 2 and 3

What about posting the solution you used for other users as a reference?

But do someone know how to set the grey oval shape cover the today date only?

When I ran the control with my code today´s date was circled in red - I doubt anything else will be available from the control. And that´s the caveat of the control as you will not see which date was selected (you would need to push that date into a label or textbox or variable to further work with it and know what has been chosen - if I understand the control correctly).

Ciao,
Holger

**jasonlewis** · 07-15-2013, 10:45 PM

as for the problem2, I use monthview_control to enter the date.
as for the problem3, I use if to make condition.

And I want to explain more about what I am doing,
For instances, when the user enter the data in 11/7, I wanna the grey oval shape to cover 11/7, so they dun need to select today date again and again. And when the user enter the data in 15/7, I wanna the grey oval shape to cover 15/7.

**jasonlewis** · 07-17-2013, 05:39 AM

Now, I realise a new problem of "how to restrict the date format",
I dun know why I enter the date in the monthview_control, it output to text format, not the date format..
Thus, how can I restrict the date format?

**jasonlewis** · 07-17-2013, 11:25 PM

Does someone know how to solve this problem?

**jasonlewis** · 07-19-2013, 09:47 AM

Does someone know how to solve this problem?

how to restrict the date format and assign the autonumber automatically for each column?

LinkBack

Thread Tools

Rate This Thread

Display

how to restrict the date format and assign the autonumber automatically for each column?

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Re: how to restrict the date format and assign the autonumber automatically for each colum

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions