How do I alter an Input Box Message

[Mister H]

Hi:

I have been serching for a while but can not find what I am looking for as the serch is returning too many hits. Hopefully someone here can redirect me to a post or offer a suggestion

I am trying to include a date in my Input Box message. Can it be done? The date is always stored in cell E2 of the Receipt Register sheet and I prefer it to appear in the message in the format yyyy/mm/dd if possible.

Here is my Input Box Message:

GrandTotal = Application.inputbox("Enter the GRAND TOTAL of the Daily Summary Report for:  ", "Enter Amount PLEASE.", "") [B]'[COLOR=red], Type:=1) [/COLOR][/B]
[B][COLOR=red]'I removed  Type:=1 and changed LONG to STRING in the DIM[/COLOR][/B]
[B][COLOR=red]' because I could not get it to pick up the numbers AFTER the[/COLOR][/B]
[B][COLOR=red]'Decimal[/COLOR][/B]

Here is the whole code I am using.

ANOTHER PROBLEM I am having is that I would like the Input Box to accept a NUMBER ONLY. Currently I can type ion 123.45aaa and it accepts it. I did change:
Dim GrandTotal As Long
to
Dim GrandTotal As String

When I used Long it would not pick up the numbers after the decimal.

Sub InputBoxTEST()
 
    Dim i As Long
    Dim GrandTotal As String 'Long
 
i = Range("H64536").End(xlUp).Row
 
'Find last row with data in column G
    LastRow = Range("G65536").End(xlUp).Offset(3, 0).Row
    Range("G" & LastRow) = "GRAND TOTAL from the Daily Summary Report:"
 
'Add formula to Sum up Column H
    LastRow = Range("H65536").End(xlUp).Offset(3, 0).Row
 
    GrandTotal = Application.inputbox("Enter the GRAND TOTAL of the Daily Summary Report for:  ", "Enter Amount PLEASE.", "") ', Type:=1)
If IsNumeric(Application.Match(False, GrandTotal, 0)) Then
    MsgBox "Not All Entries Complete - Action Cancelled", vbCritical, "Incomplete"
Else
    LastRow = Range("H65536").End(xlUp).Offset(3, 0).Row
    Range("H" & LastRow).Select
 
    Range("H" & LastRow) = GrandTotal
 
  End If
 
End Sub

ANY guidance as always is GREATLY Appreciated

Take Care,
Mark

 

[Andrew Poulsom]

Try:

    Dim GrandTotal As String
    GrandTotal = InputBox("Enter the GRAND TOTAL of the Daily Summary Report for: " & Format(Worksheets("Receipt Register").Range("E2").Value, "yyyy/mm/dd"), "Enter Amount PLEASE.", "")
    If Not IsNumeric(GrandTotal) Then
        MsgBox "Not All Entries Complete - Action Cancelled", vbCritical, "Incomplete"
    Else
'       Code if user enters a number
    End If

 

[Mister H]

THANK YOU Andrew. You have fixed BOTH problems. I ALWAYS appreciate your expertise.

The date yyyy/mm/dd is showing as: 2011-07-13

BUT I am pretty sure that is due to my Global Setting having - as the seperator. No Biggie I can work with that

Here is the final code:

Sub InputBoxTEST()
    
    Dim i As Long
    Dim GrandTotal As String
       
i = Range("H64536").End(xlUp).Row
    
'Find last row with data in column G
    LastRow = Range("G65536").End(xlUp).Offset(3, 0).Row
    Range("G" & LastRow) = "GRAND TOTAL from the Daily Summary Report:"
    
'Add formula to Sum up Column H
    LastRow = Range("H65536").End(xlUp).Offset(3, 0).Row
    
    'Dim GrandTotal As String
    GrandTotal = inputbox("Enter the GRAND TOTAL of the Daily Summary Report for: " & Format(Worksheets("Receipt Register").Range("E2").Value, "yyyy/mm/dd"), "Enter Amount PLEASE.", "")
    If Not IsNumeric(GrandTotal) Then
        MsgBox "Not All Entries Complete - Action Cancelled", vbCritical, "Incomplete"
    Else
'       Code if user enters a number
    LastRow = Range("H65536").End(xlUp).Offset(3, 0).Row
    Range("H" & LastRow).Select
       
    Range("H" & LastRow) = GrandTotal
    End If
  
End Sub

THANKS Again Andrew,
Have a GREAT day,
Mark