Input Box

[GonzoB]

Hi Folks,

I have a vba which populates cells on 2 tabs (sheetA and sheetB) in my excel, based on data on other sheets. Now I need to enhance my macro the following way:
1. when kicking off the macro an input box should appear with 2 cells to fill in (cell1 and cell2)
2. the user has to add dates into both cells, the format has to be the following: yyyy-mm-dd (therefore there should be some validation which drops an error if the cells are empty or the dates are not in the right format
3. the existing part of the macro runs (no help required regarding this point)
4. the value which the user input into cell1 gets copied into column B, on sheetA. The value which the user input into cell2 gets copied into column B, on sheetB.

Any help would be highly appreciated!

 

[Micron]

1) AFAIK, and input box can only have one box to type in. If you want two you will have to use a form. You could present 2 input boxes and these could be of the type that waits for the user to select a range (or cell) whose address gets passed to the input box.
2) probably easier to (re-)format whatever date format is entered? AFAIK, dates in Office are based on US date format regardless of what your regional settings are.
Can 4. happen before 3.?
You would probably aid others to help you if you posted the existing code. If doing so, please paste within code tags (vba button on posting toolbar) to maintain indentation and readability. It is also possible to find code examples for virtually anything you want to do, which you could then incorporate into your own code.

 

[Joe4]

For item #2, I think using a Date Picker would be better than a free-form text box.
That way, you can ensure that they pick a valid date.

 

[Micron]

For item #2, I think using a Date Picker would be better than a free-form text box.

Is it true that this feature doesn't work in 64 bit? Maybe OP should include their Excel version and bitness in their details regardless.

 

[Joe4]

Is it true that this feature doesn't work in 64 bit? Maybe OP should include their Excel version and bitness in their details regardless.

I had not heard that before, but it does appear to be the case: How to insert calendar in Excel (Date Picker & printable calendar template).

 

[GonzoB]

Dear Joe4 and Microc,

thanks for the inputs. I think those put me on the right track. I decided not to use input boxes or user forms, but rather there will be two fields on my worksheet where users need to input the dates in the proper format.

What I'm tying to do now is the following:

1. when the macro is kicked off, the first thing should be that the code checks whether cell A1 and A2 on Sheet X is populated in the right format. This format is yyyy-mm-dd
2/a if the format is right in both cells the remaining part of the vba should run
2/b if the format is not right in any of those 2 fields (or if any of those two fields are empty) an error message should pop up

Can you help me creations this code?

 

[Micron]

Don't think I can help much with this one. Out of curiosity I used the macro recorder to see how one might deal with the custom format search. From what I know of formatting in Access, the format function converts numeric values to strings, so if that's the case in Excel you might want to watch for that if comparing cell data to the results of the format function. Anyway, if you can substitute cell values for the date I chose to test with you may be able to adapt this

    Application.findFormat.NumberFormat = "m/d/yyyy" 'substitute your format
    Cells.Find(What:="1/11/2022", After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=True).Activate

I think one has to realize that formats are not the same as values. Just because you see the format "1/11/22" does not mean the cell contains that value. It might very well be "01/11/2022"

 

[Joe4]

Dear Joe4 and Microc,

thanks for the inputs. I think those put me on the right track. I decided not to use input boxes or user forms, but rather there will be two fields on my worksheet where users need to input the dates in the proper format.

What I'm tying to do now is the following:

1. when the macro is kicked off, the first thing should be that the code checks whether cell A1 and A2 on Sheet X is populated in the right format. This format is yyyy-mm-dd
2/a if the format is right in both cells the remaining part of the vba should run
2/b if the format is not right in any of those 2 fields (or if any of those two fields are empty) an error message should pop up

Can you help me creations this code?

You can pre-format those cells any way you like (custom format of "yyyy-mm-dd" on cells A1 and A2).

I would probably use VBA code that runs on data entry to verify the entries made into cells A1 and A2 are valid dates (and not some text entry).
You can do that with code like this (right-click on the Sheet tab name at the bottom of the screen, select "View Code", and enter this code in the VB Editor window that pops up):

Private Sub Worksheet_Change(ByVal Target As range)

    Dim rng As range
    Dim cell As range

'   See if cells A1 and/or A2 updated
    Set rng = Intersect(Target, range("A1:A2"))

    If Not (rng Is Nothing) Then
        For Each cell In rng
'           If entry is not a valid date, prompt to try again
            If Not IsDate(cell) Then
                Application.EnableEvents = False
                cell.ClearContents
                Application.EnableEvents = True
                MsgBox "That is not a valid date entry!", vbOKOnly, "PLEASE TRY AGAIN!"
            End If
        Next cell
    End If
    
End Sub

Then, if they make any non-date entry in cells A1 and A2, it will clear their entry and ask them to try again.
You could also add more validation to this code, if you wanted to limit the date to a certain date range.

Then, for your other VBA code that you want to run based on these entries, you can simply add a simple little checking block at the top of it like this:

If (Range("A1") = "") Or (Range("A2") = "") Then
    MsgBox "You must populate cells A1 and A2 will valid dates before running this code!", vbOKOnly, "PLEASE TRY AGAIN!"
    Exit Sub
End If

 

[GonzoB]

Thank you for the help Joe and Micron.

 

[Joe4]

You are welcome.
Glad we were able to help!