How restrict copy data from form to sheet based on five days within current month

[Ali M]

Hello,

based on these codes will copy data from listbox1 based on current month for current year.

what I Look for when try copy data to sheet should restrict for days 27,28,29,30,31 for current month for current year.

example : date(today) 17/11/2024 and when select NOV-24 then will not copy to sheet and inform me " you need to reach to 27/11/2024 at least to copy data, remaining days will be 10 days to allow that".

if reach to 27/11/2024 then will copy to sheet

if run again for 27/11/2024 then shouldn't copy again and inform me " the data have ever copied"

if run for 28 or 29 or 30 or 31 then will not copy as long have already copy in 27/11/2024( rather than should work for only one day and for one time within five days for current month for current year .

Private Sub CommandButton1_Click()
    Dim i           As Long
    
    Dim lastrow      As Long
    lastrow = sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1

    With Me.ListBox1
        
        For i = 0 To .ListCount - 1
            sheet2.Cells(lastrow, 1).Value = ComboBox1.Value
            sheet2.Cells(lastrow, 2).Value = .List(i, 0)
            sheet2.Cells(lastrow, 3).Value = .List(i, 1)
            sheet2.Cells(lastrow, 4).Value = .List(i, 3)
            lastrow = lastrow + 1
        Next i

    End With
End Sub

Private Sub UserForm_Activate()



Dim MyDate As Date
    Dim i As Integer
    MyDate = Date
    For i = 1 To 12
        Me.ComboBox1.AddItem Format(MyDate, "mmm-yy")
        MyDate = DateAdd("m", -1, MyDate)
    Next

End Sub

 

[Rijnsent]

Hi Ali,
I don't understand your problem completely (e.g. I don't see how you know the data has been copied), but this code will probably help you:

'To check the value of Combobox1
If Month(ComboBox1.Value) = Month(Date) And Year(ComboBox1.Value) = Year(Date) Then
    'selected date in combobox is in the same month & year as today
    If Day(ComboBox1.Value) >= 27 Then
        'needs a check to see if it has been copied to sheet...
        
    Else
        MsgBox "Warning, you need to reach to " & Format(DateSerial(Year(Date), Month(Date), 27), "dd/mm/yyyy") & " at least to copy data, remaining days will be 10 days to allow that", vbExclamation + vbOKOnly
        Exit Sub
    End If
Else
    'Another month (earlier or later than today) has been selected, do something else?
    
End If

 

[Ali M]

thanks
can you guide me where is my mistake, please?!
mismatch error

If Month(ComboBox1.Value) = Month(Date) And Year(ComboBox1.Value) = Year(Date) Then

this is what I did it

Private Sub CommandButton1_Click()
  Dim i           As Long
   
    Dim lastrow      As Long
    lastrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1
'To check the value of Combobox1
If Month(ComboBox1.Value) = Month(Date) And Year(ComboBox1.Value) = Year(Date) Then
    'selected date in combobox is in the same month & year as today
    If Day(ComboBox1.Value) >= 27 Then
        'needs a check to see if it has been copied to sheet...
         With Me.ListBox1
       
        For i = 0 To .ListCount - 1
            Sheet2.Cells(lastrow, 1).Value = ComboBox1.Value
            Sheet2.Cells(lastrow, 2).Value = .List(i, 0)
            Sheet2.Cells(lastrow, 3).Value = .List(i, 1)
            Sheet2.Cells(lastrow, 4).Value = .List(i, 2)
            Sheet2.Cells(lastrow, 4).Value = .List(i, 3)
            lastrow = lastrow + 1
        Next i

    End With
    Else
        MsgBox "Warning, you need to reach to " & Format(DateSerial(Year(Date), Month(Date), 27), "dd/mm/yyyy") & " at least to copy data, remaining days will be 10 days to allow that", vbExclamation + vbOKOnly
        Exit Sub
    End If

   
End If
  
End Sub

I don't see how you know the data has been copied

will be from listbox after running the form will show data by another code

 

[Ali M]

DELETED