UserForm1 issues

[Porkie18]

Hi I use a UserForm in my spreadsheet but I need a little bit of help.
I use a MONTHLY Form which needs to be changed in the UserForm from say Sheet1 to Sheet 2 (January to February) each end of month.

Obviously this is a 12 Month (Yearly) Spreadsheet and I'm wondering is there a way of making the Userform change so that at each first of the month it places the data this form collects in to the right Month?

I do not know a great deal in VBA and I created this Spreadsheet to make it easy but as with the change of month I seem to have to go into the Form and change the details of sheet I wish to use.

I was going to create 12 UserForms with the correct Sheet Activation in each for each month but I am thinking there may be a better or less time consuming way of doing this.

Thank you in advance

 

[Porkie18]

Hi Yes sorry (I did say I was new at this) yes the UserForm allows manual entry of certain data including DATE.
Does this make it more problematic?

 

[My Aswer Is This]

So if you plan to enter the Date Manually why not just enter the sheet name in some Textbox.

If you want data entered into sheet named May then enter May into Textbox22 or what ever.

Is this what your wanting. You want to enter the sheet name manually into A Textbox

If so tell me the textbox Name where you plan to enter the sheet name.

 

[My Aswer Is This]

You originally said:

Obviously this is a 12 Month (Yearly) Spreadsheet and I'm wondering is there a way of making the Userform ​

change so that at each first of the month it places the data this form collects in to the right Month?

You never said you wanted to manually enter the Month.
​

​

 

[Porkie18]

Hi would it better to sent the spreadsheet I'm trying to work with so you can see what I mean?

It has TABS for each month and an enter box in each of these (UserForm1). All works apart for the getting it in to the correct month from a date. I do not know very much VBA and am learning all the time but sadly cannot answer this, if indeed there is such a way.

 

[My Aswer Is This]

I'm surely not understanding what you want.

Your now saying:

and an enter box in each of these

What is a enter box Do you mean A TextBox?

And what does each of these mean?

I never open files posted on this forum.

Are you saying now you have twelve UserForms one for each Month.

See when you say I have a Box and do not give the name of the Box it does not help me.

And Excel does not have Boxes.

Excel has TextBoxes ComboBoxes and Listboxes

You need to get use to using proper excel Terminology.


​

 

[My Aswer Is This]

The only thing I changed in your code was how to know what sheet to enter this data in.

You said in post one if I understand it properly you said put data in this Months sheet.
And you said sheets were named like: Jan Fed Mar Apr.

And the script I provided did just that. So I'm not sure what the problem is.

If today is April 4 2019 then if script is run on April 4 2019 the data will be entered in sheet named Apr

If today is May 4 2019 the script will put data in sheet named May

The Date is not entered in any Box as you call it. The script automatically knows what Todays Date is.

 

[Porkie18]

Hi Sorry for delay.
Basically I have a spreadsheet with 12 tabs - one for each month. I designed a UserForm via the usual command/text boxes to simplify the idea of required data input that was required to be captured in the form. I thought it would save time.
This userform indeed does as I intend but it is the change of month I'm trying to work with as presently I go in and change the Activate Sheet number from 1 (Jan) to 2 (Feb) and so on through the year.

I asked for a DATE using two digits for the DAY and 1 or two digits for the month leaving Excel to work out that the year is the same as the one it knows.
The rest are simple TEXT BOXES capturing requirements.

Now the code you fine fellows (thank you )have presented me works but obviously I did not give you enough information and hopefully have done this now as above.

I take the point of the date (leaving excel to produce the date-stamp on entry) so now to get all to work would I: -
1. Be better off taking the date out as the date is the date of entry of data
2. Try a different formula for date entry and leave the UserForm as is.

In all of the two pints above I have to say that we also enter a further date when the Vehicle returns to Service and if Excel places a date in for me would it not generate a different date everyday when the sheet is re-opened and it may simple paste the cell with Today() rather than manual input?

Thanks you for your patience

 

[My Aswer Is This]

I see you just joined the Forum yesterday and it seems as if we have not provided a answer which helps you.

I thought all you wanted was a way to have the script refer to your sheet names different for every month.

And you said your sheet names looked like this:
From your earlier post:

JAN, FEB APR MAY JUN JUL AUG

My script I provided does exactly that but after reading your last post I'm not really sure what your question is any more.

So hopefully someone else here on this forum will be able to help you.

​

 

[Porkie18]

Hi, I'm sorry if I confuse people here and it was never my intention to do so but I know very little about VBA and coding other than what I have learned myself.

You Script does what you designed it to do yes it does but as you pointed out I did not mention manual entry of the date nor the fact that I was using a 12 TAB Sheet for the Year.

My basic question was: -
Is there anyway I can modify my UserForm to accommodate date changes so that when I enter a Date in the TEXT BOX in Userform so it will determine that date and place it in the relevant sheet for the month as typed in textbox1 rather than each end of month going and changing the Activate Line?

I m sorry I did not provide the fact that I am using a 12 Tab Workbook nor that I manually enter the date in day/month format.

Your Code did do as I wished except because of my neglect to inform about manual entry of the date I found that when I did enter a date it still went to Sheet1 (JAN) as it somehow worked out that was the next available clear line regardless of the manual date entered.

I am sure (which I am still playing with) it will do as it says on the tin and I sincerely thank you thus far once I lose the date input by manual entry but work pressures have not allowed me to play fully with it.

Does this help explain it and allow you to forgive my ignorance?

 

[My Aswer Is This]

You just said:

rather than each end of month going and changing the Activate Line?

The script I provided does not require you to manually change anything.

If you run the script today it will enter values in sheet named "Apr"

If Next month you run the script it will enter data in sheet named "May"​

And the script I provided should always enter the date in the proper sheet.

If you want to manually enter the date. Why not just enter the sheet name in A TextBox.

Are you now wanting to do this manually? If so what is the name of the TextBox where you plan to enter "Jan" or "Feb"


I think I have said this several times already.

If you want to do this manually the tell me in What Textbox you plan to enter "Jan"
Or "Feb"


​