Modifying code for message box on userform based on selected combobox

Ali M

Active Member
Joined
Oct 10, 2021
Messages
359
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Hello ,
what I look for when there is no any data inside the form sheet and select month from combobox then should copy to FORM sheet from the first time ignoring all of conditions inside the code.
be carful don't change any condition inside the code I really need it.
just when clear data inside sheet and try to copy selected month from combobox from first time when there is no data inside the sheet ,then should copy to inside the sheet , after there is data in sheet will implement as the original code does it .
here is the procedure what I'm talking about .
also posted here
copy selected month from combobox for just from first time when there is no data

VBA Code:
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"
    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
    For i = 2 To lastrow
'        If sheet2.Cells(i, 1).Value = currentMonth Then
'            alreadyCopied = True
'            Exit For
'        End If
        ' check if previous month or current month was copied
        If sheet2.Cells(i, 1).Value = prevMonth Then previousCopied = True
        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
        'Exit Sub
    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"
                Else
                ' adjust lastrow to avoid gap for first data set
                lastrow = 0
            End If
            ' 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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Forum statistics

Threads
1,226,017
Messages
6,188,439
Members
453,474
Latest member
th9r

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top