VBA - Numeric value as String

NessPJ

Active Member
Joined
May 10, 2011
Messages
414
Office Version
  1. 365
Hi all,

I have a (numeric) value in VBA declared as String, yet when user inserts the number in the Inputbox Excel still seems to store this as a number.

Ergo an input like "010120" will result in a value of "10120".

Is there any way around this ?

Code:
Sub Invoer()

Dim THT1 As String, DataCheck As String

THT1:
THT1 = InputBox("Voer of scan de THT Datum in van Pallet 1 (DDMMYY)", "THT invoeren (DDMMYY)")

    If StrPtr(THT1) = 0 Then
        Exit Sub
        
    ElseIf THT1 = vbNullString Then
        MsgBox "Deze waarde mag niet leeg zijn! Vul een geldige waarde in!", vbCritical, "Ongeldige waarde"
        GoTo THT1
        
    End If

'THT 1:
DataCheck = InStr(1, THT1, "(15)")

If DataCheck > 0 Then
    THT1 = Mid(THT1, InStr(THT1, "(15)") + Len("(15)"), 6)
Else
    THT1 = THT1
End If

Sheets("Menu").Range("H6").Value = THT1

End
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi
what about below
Please Feedback

thanks

VBA Code:
Sub Invoer()

Dim THT1 As String, DataCheck As String

THT1:
THT1 = InputBox("Voer of scan de THT Datum in van Pallet 1 (DDMMYY)", "THT invoeren (DDMMYY)")

    If StrPtr(THT1) = 0 Then
        Exit Sub
       
    ElseIf THT1 = vbNullString Then
        MsgBox "Deze waarde mag niet leeg zijn! Vul een geldige waarde in!", vbCritical, "Ongeldige waarde"
        GoTo THT1
       
    End If

'THT 1:
DataCheck = InStr(1, THT1, "(15)")

If DataCheck > 0 Then
    THT1 = Mid(THT1, InStr(THT1, "(15)") + Len("(15)"), 6)
Else
    THT1 = THT1
End If
    With Sheets("Menu").Range("H6")
        .Value = THT1
        .NumberFormat = WorksheetFunction.Rept(0, Len(THT1)) '
    End With
End Sub
 
Upvote 0
Since it seems you want it to be stored as Text, I'd suggest:

Code:
With Sheets("Menu").Range("H6")
   .Numberformat = "@"
   .Value = THT1
End With
 
Upvote 0
Since it seems you want it to be stored as Text, I'd suggest:

Code:
With Sheets("Menu").Range("H6")
   .Numberformat = "@"
   .Value = THT1
End With

Hello guys,

Thanks for your input and quick response! I went for the set Numberformat method and it works!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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