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

 

[gallen]

Hello and welcome.

Automatically selecting the right sheet based on the current date shouldn't be an issue, certainly 12 userforms wouldn't be the solution.

Do you have the current code? If so please post here using the code tags and I'm sure someone will be able to point you in the right direction.

 

[My Aswer Is This]

So you have some code in a userform button that refers to a different sheet depending on the current Month is that true?

Show us the code that refers to the Sheet.

And show us a example of exactly how your sheets are named.

 

[Porkie18]

The Code is: -
Private Sub CommandButton1_Click()
Dim emptyRow As Long


'Make Sheet1 active
Sheet1.Activate (This is the Activate Sheet of course and the VBA is very basic as I had to learn it as I went along - Sorry)


'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1


'Transfer information
Cells(emptyRow, 1).Value = TextBox1.Value
Cells(emptyRow, 2).Value = TextBox2.Value
Cells(emptyRow, 3).Value = TextBox3.Value
Cells(emptyRow, 4).Value = TextBox4.Value
Cells(emptyRow, 5).Value = TextBox5.Value
Cells(emptyRow, 6).Value = TextBox6.Value
Cells(emptyRow, 7).Value = TextBox7.Value
Cells(emptyRow, 8).Value = TextBox8.Value
Unload Me
End Sub


Private Sub CommandButton2_Click()
Unload Me
End Sub


Private Sub CommandButton3_Click()
Unload Me
UserForm1.Show


End Sub


Private Sub Label8_Click()


End Sub


Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Mid(TextBox1.Value, 4, 2) > 12 Then
MsgBox "Small Error -Invalid date, please re-enter Mark M", vbCritical
TextBox1.Value = vbNullString
TextBox1.SetFocus
Exit Sub
End If

dDate = DateSerial(Year(Date), Month(Date), Day(Date))
TextBox1.Value = Format(TextBox1.Value, "mm/dd/yyyy")
dDate = TextBox1.Value
End Sub


Private Sub UserForm_Click()


End Sub


Private Sub UserForm_Initialize()


End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = 0 Then
Cancel = True
MsgBox "The X is disabled, Use Cancel Entry To Exit. Mark M", vbCritical
End If

End Sub

Any help greatly received

 

[Porkie18]

Hi,So
Yes I wish to try and use the same UserForm so that when it clicks over in to a new month it then somehow searches the Workbook to point the captured data from the UserForm in to the correct Month based on date entry (perhaps?) rather than keep changing the Activate string or create further ones. I have looked at creating a copy of the userform but even in changing the name of the form it still defaults to UserForm1 and still loads in to the Sheet the activation line states. Annoying.

thanks again

 

[gallen]

Hi Porkie,

thanks for that,

As stated in post #3 , we'd need to know the naming format of each sheet. Is it literally just the full month name?

 

[Porkie18]

Hi Yes it is short MONTH Names such as JAN, FEB APR MAY JUN JUL AUG etc.

 

[My Aswer Is This]

You need your script to look like this:

Private Sub CommandButton1_Click()
'Modified  4/4/2019  6:55:22 AM  EDT
On Error GoTo M
Dim emptyRow As Long
Dim ans As String
ans = MonthName(Month(Date), True)
emptyRow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
With Sheets(ans)
    .Cells(emptyRow, 1).Value = TextBox1.Value
    .Cells(emptyRow, 2).Value = TextBox2.Value
    .Cells(emptyRow, 3).Value = TextBox3.Value
    .Cells(emptyRow, 4).Value = TextBox4.Value
    .Cells(emptyRow, 5).Value = TextBox5.Value
    .Cells(emptyRow, 6).Value = TextBox6.Value
    .Cells(emptyRow, 7).Value = TextBox7.Value
    .Cells(emptyRow, 8).Value = TextBox8.Value
End With
Unload Me
Exit Sub
M:
MsgBox "That sheet name does not exist"
End Sub

 

[Porkie18]

Hi, Is this long MONTH Name or first three letters as when I try APR dates that goes there but as soon as I use 05/11/2019 it still puts it in April?

Code says MonthName(month(Date), True).

 

[My Aswer Is This]

I thought you wanted data put in sheet named Current Month.
Current Month Now is Apr

You did not say you wanted to manually put in Date

To test my script if you want use this:

ans = MonthName(Month(Date + 35), True)

This would give you Month for Today plus 35 days.
Which would be May
Are you now saying you want to always manually enter the date in some TextBox