0

I'm using a userform to add numbers in my excel sheet.

When someone add 01234 in the userform, I only see 1234 in the excel cell.

So I modified the cell format, it worked but whenever i wanted to only add 1234 , excel would display a 0 so 01234 .

I just want to be able to add the textbox value as it is in excel sheets.

Thank you

EDIT:

This is a part of the code that takes the textbox value and enters in the requested cell : 

Sheets("Sheet3").Range("B4").Select
ActiveCell.Value = UCase(TextBox2)
Chadi N
  • 439
  • 3
  • 13

2 Answers2

0

Prefix zero-prefixed entries with ' (single quote), no prefix for non-zero prefixed entries?

   A
1'01234
2 1234

=A1+A2->2468 (OK)

Rafael
  • 1
  • 1
  • But `=SUM(A1:A2)` doesn't return 2468. – BigBen Feb 19 '20 at 21:09
  • @BigBen true (but `Sum(--A1:A2)` does), but the Q is unclear on if that's an issue, and other matters. – chris neilsen Feb 19 '20 at 21:15
  • @chrisneilsen - yes I agree it's unclear. Rafael - my point was that maybe it's best to avoid mixing numbers stored as text, and true numbers, in a column. – BigBen Feb 19 '20 at 21:16
  • @chrisneilsen - noting that you might need to CSE for SUM(--A1:A2) to work. – BigBen Feb 19 '20 at 21:17
  • @BigBen, yep, (I did test before commenting) without CSE (But I am using a version with Dynamic arrays, that may be a factor) – chris neilsen Feb 19 '20 at 21:18
  • Yeah - I have to CSE, changed my comment. Assuming you're working in the latest version of Excel? Ah, yes you are. I needed CSE in Excel 2016. – BigBen Feb 19 '20 at 21:18
  • @BigBen Do you need to preserve leading zero information? i.e. can the leading zeroes be discarded? If you do not, then all you would need to do is 'cast' the zero-prefixed entries (text) to numbers (thus losing leading zero information and keeping the data in a homogeneous number format). If you need to keep that information (leading zeroes) you would then need to do the opposite: cast numbers to text, thus having everything as text. – Rafael Feb 19 '20 at 21:31
  • @Rafael - I don't know - I didn't post the question - but I agree with you. – BigBen Feb 19 '20 at 21:31
  • 1
    @chrisneilsen Dynamic Arrays really changes things. We now have to remember how hard it used to be, since things are so much easier with DA. – teylyn Feb 19 '20 at 21:39
  • sorry guys, i was at work. Just finished. I need to preserve the leading zero in the cell. If we enter "01234" in the textbox, i need to see "01234" in the cell, and when we enter "5678" in the textbox, i need to see "5678" in the cell. I need to see the exact value intered in the textbox. @Rafael I edited my question too. – Chadi N Feb 19 '20 at 21:46
  • @SandraGevio Thank you for the clarification. Any other thoughts, taking the clarification into account guys? – Rafael Feb 19 '20 at 21:54
  • @SandraGevio the most important claification we need is: Do you need the textbox content in the Cell _as a Number_ (when it _looks_ like a number) or always as Text. If the later, use `SomeCell.Value = "'" & UCase(TextBox2)`on all cells. FYI [see this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) regarding `Select` – chris neilsen Feb 19 '20 at 22:03
  • @chrisneilsen It doesnt matter in which format, as long as I see the leading 0 if the users entered that number.. Im sorry if i'm not 100% clear, im french :) – Chadi N Feb 19 '20 at 22:35
0

Since you say (in comments) that you don't mind if the resultant cell value is numeric or text, simply use

Worksheets("Sheet3").Range("B4").Value = "'" & UCase(TextBox2)

This will make all entries text. FWIW, that may become an issue for you later!

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • Why would it become an issue? Thank you for your answer, im trying now! – Chadi N Feb 19 '20 at 22:48
  • It would become an issue _if_ you want to use those cell values _as numbers_ – chris neilsen Feb 19 '20 at 23:11
  • I only need to store the numbers.. but now i'm being skeptical. Should I keep it that way? I added a number as a test and it's working, but in the function bar i see `'01235`. I'm wondering if it would pose a certain problem in the future if I need to use it for something else.. What do you think? These numbers are simply a code for machine parts. Every part needs its own code, which we generate. We wont use these numbers for mathematical purposes... – Chadi N Feb 19 '20 at 23:27
  • @SandraGevio if these are in fact AlphaNumeric codes (which happen to not include any Alphas') then storing as text should be fine. At some point you'll probably want to do a lookup using `Match`, `VLookup`, `XLookup` etc, so it's important that _all_ codes are stored in the same format (ie Text, as my answer will do. Just make sure that _all_ codes are stored like that) at which point you'll need to ensure the value you try to look up is also Text – chris neilsen Feb 20 '20 at 00:40
  • Ok got it.. it's perfect then. Thank you for your time! Life saver!! – Chadi N Feb 20 '20 at 01:58