VBA format entry

[cyrilbrd]

Hi,
Given a UserForm with a TextBox named TextBox1
The textbox will feed data to an excel table using the following code:

Sheets("Data").Range("Data_Start").Offset(TargetRow, 6).Value = TextBox1

We would like the user to encode 6 numbers example 123456 that would show in the table as "STATUS-12-3456"

So if 230123 was typed in the textbox, the following would appear in the table "STATUS-23-0123"
Any advice?

 

[Phuoc]

Try this:

Sheets("Data").Range("Data_Start").Offset(TargetRow, 6).Value = "STATUS-" & Format( TextBox1,"00-0000")

 

[dmt32]

Hi
if you want the value in the cell to remain numeric (like a date) then you can apply a Number Format

With Worksheets("Data").Range("Data_Start").Offset(TargetRow, 6)
        .Value = TextBox1.Value
        .NumberFormat = """STATUS-""00-0000"
    End With

Dave

 

[cyrilbrd]

Thank you for the proposed solutions, I can use the second one for another entry that would require the cell to remain numerical.

 

[cyrilbrd]

Given the above. The users are allowed to recall / edit previously entered information.
The code used for recalling TextBox1 is:

Data_UF.TextBox1= Sheets("Data").Range("Data_Start").Offset(TargetRow, 6).Value

How can I force a format as the data is modified?
Example above the data should show "STATUS-12-3456"
Further for value with several decimal point or integer what would be the best method to show numerical instead of text?
Example

Data_UF.Txt_Size = Sheets("Data").Range("Data_Start").Offset(TargetRow, 4).Value

Shows the result as a text instead of let's say a 4 decimal place integer.

 

[cyrilbrd]

Shall I use CDbl(Txt_Size) or is there a better way?

 

[cyrilbrd]

Sheets("Data").Range("Data_Start").Offset(TargetRow, 4).Value = Format(Txt_Size, "#.####")

Would that be the best approach?

 

[dmt32]

Given the above. The users are allowed to recall / edit previously entered information.
The code used for recalling TextBox1 is:

Data_UF.TextBox1= Sheets("Data").Range("Data_Start").Offset(TargetRow, 6).Value

How can I force a format as the data is modified?

Use the Range.Text property

Data_UF.Txt_Size = Sheets("Data").Range("Data_Start").Offset(TargetRow, 4).Text

This returns what you see in the cell rather that its underlying value

Dave

 

[dmt32]

Thank you for the proposed solutions, I can use the second one for another entry that would require the cell to remain numerical.

With Worksheets("Data").Range("Data_Start").Offset(TargetRow, 6)
        .Value = Val(TextBox1.Value)
        .NumberFormat = """STATUS-""00-0000"
    End With

I got caught out by 10 min editing rule & omitted inclusion of Type Conversion to coerce text to numeric value.
Suggest add text shown in red.

Glad suggestion helps

Dave

 

[cyrilbrd]

Thank you, Val() is noted.
This is really helpful.