Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

update condition by ignoring previous month on userform

0

Hello

I would still  update condition through ignore showin g previous month when try copying data for current month . this case is  when select month from combobox1 and there is no data in FORM sheet then should ignore showing warining previous month totally . after copy to form sheet and try to copy current month ever time then should  implement howing warining previous month condition .

again ignore showing previous month  condition when try copying data from userform from first time and doeasn't contain data in FORM sheet.

result I put when copy data from userform to FORM sheet ignoring previous month condition  just when there is no data in FORM sheet .

thanks

Answer
Discuss

Discussion

Ali M

Sorry but your question doesn't make much sense (at least from a quick look). Can you make it clearer please (perhaps with an example)?
John_Ru (rep: 6612) Jan 29, '25 at 2:16 pm
Hi John,
ok I put some examples and comments inside file for each example.
so previuos month condition should start after first month I select from form.
don't show previuos month condition before first month when try copying to the first month when there is no data inside sheet , other wise when there is first month then implement previuos month condition.
Ali M (rep: 36) Jan 30, '25 at 2:45 am
Thanks for adding the examples but I'm not sure when I will get to look at thos properly- it could be Saturday or even Monday.
John_Ru (rep: 6612) Jan 30, '25 at 9:18 am
Aki, I've revised my Answer. 
John_Ru (rep: 6612) Feb 8, '25 at 3:00 pm
Add to Discussion

1

Hi again Ali M

Correction #1 08 February 2025

Unfortunately the wording of your Question (and Discussions) is confusing, even though the Question is based on a previous Answer from me.

You have confirmed that want to have a FORM sheet for just one calendar year and - in January say- don't want to test/copy the data for the preceding December month.

Accordingly I've modified the code so that the flag "previousCopied" is set if Jan is selected in ComboBox1 .I've also changed some other bits of the code so it works as your examples file showed (see changes in bold):

Option Base 1

Private Sub CommandButton1_Click()
    Dim i As Long
    Dim lastrow As Long
    Dim sheet2 As Worksheet
    Dim currentMonth As Date
    Dim copyDate As Date
    Dim remainingDays As Long
    Dim headersWritten As Boolean

    Set sheet2 = ThisWorkbook.Sheets("FORM") ' Replace with the correct sheet name if necessary

    ' lastrow line moved down

    ' Check if headers are already written
    headersWritten = (sheet2.Cells(1, 1).Value = "MONTH" And sheet2.Cells(1, 2).Value = "ITEM" _
                      And sheet2.Cells(1, 3).Value = "NAME" And sheet2.Cells(1, 4).Value = "BALANCE")

    ' Write headers if not already present
    If Not headersWritten Then
        sheet2.Cells(1, 1).Value = "MONTH"
        sheet2.Cells(1, 2).Value = "ITEM"
        sheet2.Cells(1, 3).Value = "NAME"
        sheet2.Cells(1, 4).Value = "BALANCE"
        ' set headerswritten flag
        headersWritten = True
    End If

    ' Determine the last row
    lastrow = sheet2.Cells(sheet2.Rows.Count, 1).End(xlUp).Row

    ' Check if this month has already been copied
    currentMonth = WorksheetFunction.EoMonth(Date, -1) + 1
    Dim alreadyCopied As Boolean, previousCopied As Boolean, prevMonth As Date

    prevMonth = WorksheetFunction.EoMonth(Date, -2) + 1
    alreadyCopied = False
    previousCopied = False

    ' Force flag if January was selected in ComboBox1
    If Left(ComboBox1.Text, 3) = "Jan" Then previousCopied = True

    For i = 2 To lastrow
        ' check if previous month was copied...
        If sheet2.Cells(i, 1).Value = prevMonth Then previousCopied = True
        'check if current month was copied
        If sheet2.Cells(i, 1).Value = currentMonth Then alreadyCopied = True
    Next i

    If alreadyCopied Then
        MsgBox "The data has already been copied for this month!", vbExclamation
        Exit Sub
    End If

    If previousCopied = False Then
        MsgBox "The data for " & Format(prevMonth, "Mmm-yy") _
        & " cannot be found." & vbCr & "This will be copied first of all", vbExclamation
        ' jump to placeholder to insert data
        GoTo DoPrevious
    End If

    ' Check if today is within the allowed days
    copyDate = Date
    If Day(copyDate) < 27 Then
        remainingDays = 27 - Day(copyDate)
        MsgBox "Warning: You need to wait " & remainingDays & " more days to copy the data.", vbExclamation
        Exit Sub
    ElseIf Day(copyDate) >= 27 And Day(copyDate) <= 31 Then
' placeholder for copying missed previous month
DoPrevious:

        ' Copy data from ListBox1 to Sheet2
        With Me.ListBox1
            ' write headers if not already done
            If headersWritten Then
                sheet2.Cells(lastrow + 1, 1).Value = "MONTH"
                sheet2.Cells(lastrow + 1, 2).Value = "ITEM"
                sheet2.Cells(lastrow + 1, 3).Value = "NAME"
                sheet2.Cells(lastrow + 1, 4).Value = "BALANCE"
            End If

            ' adjust lastrow to avoid gap for first data set
            If lastrow = 1 Then lastrow = 0

            ' determine which month to record
            If previousCopied = False Then
                currentMonth = prevMonth
            End If
            ' write month data
            For i = 1 To .ListCount - 1
                sheet2.Cells(lastrow + i + 1, 1).Value = currentMonth
                sheet2.Cells(lastrow + i + 1, 2).Value = .List(i, 0)
                sheet2.Cells(lastrow + i + 1, 3).Value = .List(i, 1)
                sheet2.Cells(lastrow + i + 1, 4).Value = .List(i, 2)
                ' lastrow = lastrow + 1
            Next i
            ' refresh the screen
            Application.ScreenUpdating = True
        End With

        MsgBox "Data successfully copied for " & Format(currentMonth, "Mmm-yy"), vbInformation
    Else
        MsgBox "Warning: You can only copy data between the 27th and 31st of the month.", vbExclamation
        Exit Sub
    End If
End Sub

The changes are made in the new modified file attached. I also chnaged the dates in sheet SS from 2023 to 2025.

I note that the code doesn't really check that the ComboBox1 selection actually matches the current month (perhaps for test reasons) but I have NOT changed that.

Hope this helps. If so, please remember to mark this Answer as Selected.

Discuss

Discussion

thanks John ,
 I think you want to have a FORM sheet for just one calendar year and - in January say- don't want to test/copy the data for the preceding December month.
yes that's correct.

but I see the days  condition becomes different.
so the original code should reach to specific days before copying but I notice to will copy JAN-25  ignoring condition days(27:31) also if the current month is MAR and there is missed month is FEB will copy FEB and when try to copy MAR will show mismatch error in this line.
If Year(sheet2.Cells(i, 1).Value) <> Year(prevMonth) Then previousCopied = True
Ali M (rep: 36) Feb 3, '25 at 2:59 am
Thanks for the confirmation of intent, Ali. Sorry but I won't get to this until Friday or maybe Saturday.
John_Ru (rep: 6612) Feb 4, '25 at 4:19 pm
Ali

Please see Correction #1 08 February 2025 to my Answer plus the revised file.
John_Ru (rep: 6612) Feb 8, '25 at 12:24 pm
Thanks John again
I tested by making  current month is JAN will copy and if change current month is APR then will copy missed MAR month ignoring FEB month !
Ali M (rep: 36) Feb 9, '25 at 3:14 am
Ali. That's annoying- you didn't make that requirement (for more than one missing month) clear in your Question or examples. On the Forum, we don't like it when users get an Answer then expand the Question (so we have to rework the solution) .

I think I answered your question and don't have more spare time to devote to it.
John_Ru (rep: 6612) Feb 9, '25 at 6:14 am
we don't like it when users get an Answer then expand the Question (so we have to rework the solution)
I don't expand the question !
based on your answered in previous thread  will show missed months  without ignoring any missed months   and in here I just want to  implement  previous month condition   start from JAN : DEC in one year . so any missed months within one year should show them .
Ali M (rep: 36) Feb 9, '25 at 1:16 pm
Ali. 

We disagree. Your question refers to "previous month" (singular not plural). Questions should be complete and standalone (so they can be answered by anyone). Accordingly I doubt I will waste my time answering your future. questions.
John_Ru (rep: 6612) Feb 9, '25 at 3:35 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login