Button to add override date

[richardcdahlgren]

I need a button so when clicked it will open an input box where it says "Enter Date Below" and then user can enter the date and it will paste the date in cell G2. I know it isn't extremely complicated, but my VBA brain is on vacation this week.

 

[dmt32]

Hi,
Place an ActiveX CommandButton on your worksheet & then add this code to the sheets code page & see if does what you want

Private Sub CommandButton1_Click()
    Dim Entry As Variant
    
    Do
        Entry = InputBox("Enter Date Below", "Enter Date")
        'cancel pressed
        If StrPtr(Entry) = 0 Then Exit Sub
    Loop Until IsDate(Entry)

    Me.Range("G2").Value = DateValue(Entry)
End Sub

Dave

 

[Joe4]

All things equal, I would typically recommend using Excel's built-in Form Controls instead of ActiveX controls for something simple like this.

The Form Control are simpler, and some programs/computers have a trust issue with ActiveX controls and may disable them.
See: Excel Form Control vs. ActiveX Control (7 Differences) | ExcelDemy
and What is the difference between "Form Controls" and "ActiveX Control" in Excel 2010?

 

[richardcdahlgren]

I used the active X with no issues so far. I will follow the link to learn more about the difference between the two. Thank you both for the quick replies!!

 

[Joe4]

I used the active X with no issues so far. I will follow the link to learn more about the difference between the two. Thank you both for the quick replies!!

There is nothing wrong with using it, just be aware of the differences and potential issues, especially if you intend to share it with other users or run it on other systems.
Some Virus checking programs may identify/flag it.

 

[dmt32]

I used the active X with no issues so far. I will follow the link to learn more about the difference between the two. Thank you both for the quick replies!!

Me too, developed many systems using ActiveX with no real issues but always useful to be aware of potential problems. You can if wanted / needed, make code a procedure in standard module and Assign a Forms button to it.

I omitted to include displaying the existing date in InputBox & if needed then use update code

Private Sub CommandButton1_Click()
    Dim Entry As Variant
    Do
        Entry = InputBox("Enter Date Below", "Enter Date", Me.Range("G2").Text)
        'cancel pressed
        If StrPtr(Entry) = 0 Then Exit Sub
    Loop Until IsDate(Entry)

    Me.Range("G2").Value = DateValue(Entry)
End Sub

Glad we could help & appreciate feedback

Dave