Problem Writing Numeric to a Cell Formatted as General 4

[TheAceMan1]

Howdy all . . .

Have a cell formatted as [blue]General[/blue].
[ol][li]When I enter a numeric value directly thru the keyboard, all is fine. The number is formatted left justified indicating text.[/li]
[li]When I write a numeric value thru VBA of a userform, the text is as above only I get the little [green]green triangle[/green] in the top left corner of the cell ... which prompts me about the value being formatted as text. Also, a combobox of options becomes available if I set focus to the cell.[/li]
[li]Special Note: The datatype of the cell can't be numeric because the cell has to accept numeric as well as alfa-numeric values (5005775 or 05FA2).[/li][/ol]
I'd like to understand better the difference between the two data entry methods [blue]keyboard/VBA[/blue], so I can write cleaner code. Also ... in a logically correct way, how do I prevent that little [green]green triangle?[/green]

[blue]Your Thoughts? . . .[/blue]


See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SkipVought]

hi,
quote]When I enter a numeric value directly thru the keyboard, all is fine. The number is formatted left justified indicating text.
[/quote]
When a cell is formated GENERAL and you enter a numeric value, the value is NUMERIC, regardless of justification.

When you assign a cell from a control, the value you assign is a TEXT value, in this case a string of numeric characters.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TheAceMan1]

SkipVought . . .

What you posted I was thinking the same and you've supported that. So I went ahead and changed the numeric format to [blue]text[/blue]. Still I get the little [green]green triangle[/green] prompting that the number is formatted as text.

Any Ideas? ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SkipVought]

Do you have other NUMERIC values in that column?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TheAceMan1]

SkipVought . . .

The column has two cells that can be a mixture of [blue]Numeric[/blue] and/or [blue]Alpha-Numeric[/blue]. By that I mean:

[tt][blue]Row21 Row22
***** *****
Num Num
Num Alpha
Alpha Num
Alpha Alpha[/blue][/tt]

Your Thoughts? . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SkipVought]

Did you check the tip on the cell(s) in question?

AND...

why would you have a column containing NUMERS & TEXT?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TheAceMan1]

[blue] ... why would you have a column containing NUMERS & TEXT?[/blue]

The cells involve changing of an electronic board and its the Lot# of the change thats recorded. This can be numeric or alpha-numeric!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SeekExcellence]

Hi
For the green error alert, you can take off the background error checking option in 'Excel options - formulas - Error checking'

For the textbox problem, to set value to number I use this solution. It works fine for my need.On an 'exit' event in the textbox I put this code ('ArtPu' = controlsource):

Private Sub TextBoxPxU_Exit(ByVal Cancel As MSForms.ReturnBoolean)

' Replace dot for comma
Range("ArtPU").Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

' Set Cell format to number
Range("ArtPU").NumberFormat = "_-* #,##0.00 [$€-40C]_-;-*_ #,##0.00 [$€-40C]_-;_-* ""-""?? [$€-40C]_-;_-@_-"

Dim Val As Currency
Val = Range("ArtPU").Value
Range("ArtPU").FormulaR1C1 = Val

I don't how this will respond to text entries, but it sets number entries to currency or number. Maybe it helps you.


Just beginning...love to learn from you.
Thanks for replying.

 

[TheAceMan1]

How are ya SeekExcellence . . .

Thanks for the input. The problem is not the formatting of a number but rather [blue]excel detecting numeric[/blue]. Since the cells mentioned have to accept alpha-numeric as well as numeric, this perscribes a text only format. OK ... so we set text for the numeric format (thinking that should do it!). However when VBA writes [blue]numeric text[/blue] to the cells, excel still detects a number and flags the error.

I can take care of the error with [blue]Tools[/blue]-[blue]Options[/blue]-[blue]Error Checking Tab[/blue] and in the rules section ... uncheck [blue]Number stored as text[/blue] (I don't like the Idea of turning off [blue]background checking[/blue] which I could do instead). [purple]The problem with changing options is that there global to all workbooks![/purple] ... this forces me to write extra code to disable/enable the option to keep things proper. I don't think I'll be able to get around this.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[combo]

You need to format a cell as text only when you store numeric values (including date/time) as text.

If you know, on the userform, what is numeric, you can use intermediate numeric variables or conversion functions before writing values to the cell.

Can you post a line of code that transfers data from the userform to the cell?



combo

 

[DrSimon]

Hey Aceman
The Userform field obviously considers anything that goes into it a string so what about this:

    ..... code to input data into TextBox1
    If IsNumeric(TextBox1) Then
        ActiveSheet.Cells(Row, Column) = Val(TextBox1)
    Else
        ActiveSheet.Cells(Row, Column) = TextBox1
    End If

It seems to work.

 

[TheAceMan1]

To All . . .

Performing some testing. Will return with the results soon.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

To All . . .

[blue]DrSimon[/blue] hit the nail on the head with:

[blue]   If IsNumeric(TextBox1) Then
      ActiveSheet.Cells(Row, Column) = [purple][b]Val([/b][/purple]TextBox1[purple][b])[/b][/purple]
   Else[/blue]

It appears datatype casting to numeric with [blue]Val()[/blue] does the trick ... and no need to disable any options!

[blue]The above is fine with any numeric format except [purple]Text![/purple] With text selected as the format its [purple]manual data entry[/purple] that throws the error ... while using Val() in VBA works.[/blue]

Since its possible to have any number of cells with this problem and to keep from writing the [blue]If statement[/blue] so many times, I wrapped it in a function:

[blue]Public Function TypeCast(usrVal As Variant) As Variant
   
   If IsNumeric(usrVal) Then
      TypeCast = Val(usrVal)
   Else
      TypeCast = usrVal
   End If

End Function[/blue]

This transforms the code presented by [blue]DrSimon[/blue]:

[blue]   If IsNumeric(TextBox1) Then
      ActiveSheet.Cells(Row, Column) = Val(TextBox1)
   Else
      ActiveSheet.Cells(Row, Column) = TextBox1
   End If

[green]becomes:[/green]

   ActiveSheet.Cells(Row, Column) = TypeCast(TextBox1)[/blue]

Thinking about writing an FAQ on this matter. In any case thanks to all ... and espcially [blue]DrSimon![/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SkipVought]

...and its the Lot#...

Will you EVERY do arithmetic on these 'numbers'?

faq68-6659.

IMHO, you should NEVER no NEVER, well, hardly EVERY, have a column with both NUMERIC & TEXT!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TheAceMan1]

[blue]Will you EVERY do arithmetic on these 'numbers'?[/blue]

No! Can't perform any math on [blue]05FA2[/blue] ... numeric or alpha-numeric these cells simply identify the process in how the boards are made.

[blue]IMHO, you should NEVER no NEVER, well, hardly EVERY, have a column with both NUMERIC & TEXT!!![/blue]

I couldn't agree more. I know of no other place where this is allowed other than [blue]Excel![/blue] Its one of the reasons I left Excel years ago ....

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DrSimon]

AceMan - I am truly delighted to be able to help you for once. Both you and Skip have provided so much help to me in the past.

With regard to the final other point re typing - you presumably have not seen MUMPS. It has no variable types but with the ability to do things efficiently that I've not seen in any other language. Perhaps that why I came up with the KISS solution .

Simon

 

[SkipVought]

Row21 Row22
***** *****
Num Num
Num Alpha[red]
Alpha Num[/red]
Alpha Alpha

No! Can't perform any math on 05FA2

I was NOT referring to an ALPHA. I WAS referring to [red]THESE NUMERIC[/red], where you have a NUMBER in row22 and an ALPHA in row 23! This ia where you should enter these numeric identifiers as TEXT!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]