CORRECT WAY TO FILL A TEXTBOX

[Amelynn]

Hello again! I'm here with something I always use, but never seemed to understand; this is the matter:

What would be the correct and most efficient way to fill a Textbox?

I was able to fill different Textbox in various ways, but really I was just settling for it to display what I wanted. In these ways the TextBox is filled without problems:

Private Sub UserForm_Initialize()

TextBox1 = "Niebla"
TextBox2 = 7
TextBox3 = Worksheets("Sheet1").Range("B6").Value
TextBox4.Text = Worksheets("Sheet1").Range("B7").Value
TextBox5.Value = Worksheets("Sheet1").Range("B8").Value
TextBox6.Value = Worksheets("Sheet1").Range("B9").Text
TextBox7.Value = Worksheets("Sheet1").Range("B10")

End Sub

What I don't know is:

• If after TextBox(n) it is necessary to put the .text or .value
• If not putting anything is wrong (although it works)
• What is the difference between the three things.

Regarding the data that I push to the TextBox:

• Should I necessarily declare that, for example, "Niebla" is a variable of type Str?
• After the range, should I put .text, .value or just nothing?

Check that the Textbox is always filled, but I'm worried about not knowing if it's done right or what's most convenient.

I would appreciate your advice, thank you in advance.

[DocAElstein]

Hi Amelynn

…..

• If after TextBox(n) it is necessary to put the .text or .value
• If not putting anything is wrong (although it works)
• What is the difference between the three things……

I can’t answer fully your question here, unfortunately, because
_ I have no experience with Textboxes
_ I am not too familiar with the range .Text property. ** ( Note also: I think that probably the range .Text property and the textbox .Text property are seperate things. I am not familiar with either )


I can only tell you the small part that I know about: - what I know about is
Range__
Range__.Value
Range__.Value2

Excel range (Range__ object )
Range__ is an object with an extremely large number of properties, methods and various things.
Range__ is all to do with how Excel organises and uses cells. Understanding the Range__ object is probably one of the most important things to know about in Excel and VBA, especially if you are interested in spreadsheet things.
( ** Because there are so many different things associated with the range object, nobody knows all of them. .Text is just one of the many properties of the range object that I personally am unfamiliar with )
But often when we use the Range__ object, we are only interested in the value that is in a cell. Because most people are often only interested in the value in a cell, Microsoft have made .Value the default of what you get if you just use Range__. So most of the time if you choose to write just Range__ , then in fact , Excel will not see that, instead it will see and use Range__.Value
It is just personal choice if you choose to use Range__.Value or Range__. Usually there are no problems if you just use Range__ , but I have seen occasions when this caused problems as there may be occasions when Excel tries to refer to the range object instead of the value.
So personally I prefer to always include the .Value if I am interested in a value. I will only leave out the .Value if I am doing something that wants me to reference the range object. Just personal choice.

So, in your example, when you used Worksheets("Sheet1").Range("B10") , Excel did not see and use that.
Instead, Excel saw and used this: Worksheets("Sheet1").Range("B10").Value


So…

....After the range, should I put .text, .value or just nothing? ...

in your examples you could probably just use nothing , but I personally would recommend that you include .Value ( or .Value2 )
But that is just my personally recommendation

.Value or .Value2
.Value is almost the simplest cell value. But not quite. If you are interested in dates or currency, then .Value will show you the date or currency in a date or currency format.
.Value2 is the most simplest cell value as Excel has it held before any formatting is done.

Generally speaking in VBA, different cell formatting can sometimes cause awkward problems when referrencing the values of those cells. So it is better practice to do as much as possible without formatting, be it in Excel or VBA, and only then use formatting options if you need to.

Personally I will use .Value2 most of the time, because it may work a little faster or may be less likely to problems caused by awkward cell formatting issues. I think theoretically it is also a bit more efficient to use .Value2

So….

.....If not putting anything is wrong (although it works).....

It is not wrong to put nothing. But it is bad practice, as it may cause problems in other situations in Excel VBA
(More than half of people put nothing, and they will often get a problem later that they don’t understand ! )

---

....Should I necessarily declare that, for example, "Niebla" is a variable of type Str?

The way that you are using "Niebla" in your VBA coding is perfectly alright, because: Most of the time in VBA coding, if VBA sees something enclosed in quotes, _ "__" _ , like
"xyz"
, then VBA will take the value of _ xyz _ to be a string.
Even if , in your coding, you did this
"3"
, then VBA would not take the "3" as a number. It would see it as a string, just as it would see this as a string
"I have 3 Apples"


( Note that VBA is very user friendly with numbers and strings. For example if you pass it a string like "3" in a function wanting a number, then VBA will not error, but instead it will take a number 3 instead.
In many other computer languages you must be much more careful in defining precisely variable types. )

---

....What would be the correct and most efficient way to fill a Textbox?
.... but I'm worried about not knowing if it's done right or what's most convenient......

For the right hand side of your code lines, I would start using .Value2 , and then only use something else, such as .Value or .Text if you have to
I am not too sure what is best for the left hand side of your code lines, because I am not familiar with Textboxes and Textbox properties, such as the Textbox property .Text.
But I will take a geuss that .Text is best. But I am not sure.

So, something like this is my best geuss:
__ TextBoxX.Text = Worksheets("Sheet1").Range("B7").Value2

---

That is as close as I can come to answering your questions.
But I do know about Range__ , Range__.Value , Range__.Value2 quite well.
So I am happy to give you any further clarity on those things. Those things are all to do with range referencing in Excel and VBA, which is a very important thing to know about.


Alan

---

Ref
https://fastexcel.wordpress.com/2011...w-to-avoid-it/

[Amelynn]

I appreciate your time and I'll take your advice using .Text after TextBox., and .Value2 after .Range. It seems to be the most powerful way if it also takes currency and date formats.

I also stay calm with the declaration of variables. To be honest I almost never declare text or number variables (I hope excel guesses my intentions of course since it's so smart), but I saw someone do it in an example and this planted the seeds of doubt and suspicion in me.

Thank you very much for the new knowledge. You have a great forum here.

[DocAElstein]

Thx for the feedback

---

… take your advice using .Text after TextBox., and .Value2 after .Range. It seems to be the most powerful way if it also takes currency and date formats…...

Note: .Value2 does not preserve any format: Just to clarify:

.Value2 is the lowest level number, the most fundamental, the simplist, which Excel holds for the cells value. It is probably most efficient and best to use this if you can, that is to say, Use it if it gets the results that you want.

.Value is very similar to .Value2. The small difference is that it may give you date and currency values in some format. The final format you get will vary depending on your various settings in your particular Excel.
( .Value will often be used by default by Excel in some situations where you referrence the cell by the range object in coding where a value is expected, but you don't specify explicitly what value you want it to use. )

.Text is new to me. I have not used it much. But my first impression is that it would appear that it gives you the string format similar to what you actually see in the spreadsheet.


As Example:
In the attached file, I have a date in the first cell.
In that attached file is also the macro shown below. If you run that macro, it should give you three different values. Those three different values are the three value properties of the range object of the first cell, .Text , .Value , .Value2
You will not get exactly the same results as me for .Value and .Text , because the value is influenced by your internal settings and your Land version.
The value for .Value2 should be exactly the same as what I get, because: For dates in Excel, Excel holds as the most low level value, a number which starts at 1 for the date of January 1, 1900. For the date which I have in the first cell ( 9th September, 2022) Excel has the internal value of 44813.


Alan

---

Sub DateValueValue2Text()
Rem 0 Worksheets info
Dim Ws1 As Worksheet
 Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Rem 1 What does the range object hold for the value in first cell
Debug.Print: Debug.Print "1st cell  .Value  is   " & Ws1.Cells.Item(1).Value & vbCr & vbLf & "1st cell  .Value2  is   " & Ws1.Cells.Item(1).Value2 & vbCr & vbLf & "1st cell  .Text  is   " & Ws1.Cells.Item(1).Text
 MsgBox prompt:="1st cell  .Value  is   " & Ws1.Cells.Item(1).Value & vbCr & vbLf & _
         "1st cell  .Value2  is   " & Ws1.Cells.Item(1).Value2 & vbCr & vbLf & _
           "1st cell  .Text  is   " & Ws1.Cells.Item(1).Text
End Sub

Date Value2 Value and Text.jpg
https://i.postimg.cc/KjFm1988/Date-V...e-and-Text.jpg

[Amelynn]

Oh okay. So use .Value2 whenever it works for me, since it's fast.

.Value use it when I want to get some date or currency format that works for me.

Thanks for the clarification and by the way, I think the attached file is blank, although thanks to the fact that you put the code in the post, I was able to copy it and run the macro

[DocAElstein]

Hi Amelynn

Oh okay. So use .Value2 whenever it works for me, since it's fast.
.Value use it when I want to get some date or currency format that works for me.

Correct. (You might occasionally need .Text if you want the string format exactly as it looks in the cell)

---

…. I think the attached file is blank,…

Oops! – you’re right. My mistake, I am not sure what happened there,
The macro is there, ( - see ** ) , but the first cell was empty, -The spreadsheet is empty.
I have deleted the empty spreadsheet file and re uploaded the correct one in post #4

---

** By the way, the macro was in that file, but you may not have seen it. – To explain:
I have an annoying personal habit of putting macros in a worksheet object code module: A worksheet object code module’s main purpose is to let us have access to Event codings associated with a worksheet. But we can also put normal macros in them, and usually they work normally.

To see the coding in a worksheets object code module, either
_ Right click on the worksheet tab and select View Code, ( that will open the VB Editor): https://i.postimg.cc/13YZRyQr/Right-...-Show-Code.jpg
RightClickTab ShowCode.JPG

or,
_ If you are already in the VB Editor, then double click on the Worksheet shown in the left hand explorer window: https://i.postimg.cc/DzJ38Rfk/Worksh...m-VBEditor.jpg
WorksheetCodeModule fromVBEditor.jpg


It is not normal professional practice to use a worksheet object code module for normal macros. It is just my personal choice. (I am not an educated computer professional. Bear that in mind when taking any advice from me. Lol!)


In the file attached to this post, Date Value2 Value(1).xls , I also added a normal code module and put the macro in that as well: https://i.postimg.cc/g0CFR4rw/Normal...m-VBEditor.jpg
NormalModule fromVBEditor.jpg





( Just for completeness, to clarify all that, I added another macro, Sub CallMacros() . That macro, (which is in the normal code module, but could also be out in a worksheet object code module, ) , will call both macros.

Sub CallMacros() ' https://excelfox.com/forum/showthread.php/2813-CORRECT-WAY-TO-FILL-A-TEXTBOX?p=16692&viewfull=1#post16692
 Call DateValueValue2Text '           Excel will look for this macro in the normal code modules.  Usually it will find it, but you are relying on Excel geussing correctly!
 
 Call Tabelle1.DateValueValue2Text  ' You are telling Excel exactly where your macro is
 Call Modul1.DateValueValue2Text    ' You are telling Excel exactly where your macro is

End Sub

As you see, we should always explicitly refer to where the macro is, via Tabelle1. or Modul1.
https://i.postimg.cc/rwpkLKvp/Macro-...-Is-Method.jpg https://i.postimg.cc/Dy6TDqpD/Macro-...-Is-Method.jpg
MacroInWorksheetCodeModuleIsMethod.JPGMacroInNormalModuleIsMethod.JPG

I personally always include the full explicit referencing, but most people don’t. If you miss that extra Tabelle1. or Modul1. out the then mostly it will always work OK as Excel will assume you have the macro as Public in a normal code module, and it will find the right one.
(So without explicit referencing it will work OK 99.99% of the time. I am not a professional programmer and prefer my stuff to work if possible 100% of the time, so I always refer to things explicitly and try to avoid relying on Excel to guess correctly. Just a personal choice, and not the correct professional choice: A professional programmer seems to prefer things not to work sometimes, as it would appear Microsoft also prefer things not to always work sometimes, so that they have a chance to fix and update things, to keep them in a job , I expect, Lol!) )


Alan

---

File at share site:
Date Value2 Value(1).xls - https://app.box.com/s/pxy9hjqfvtyxq45g026098pxfy1j5k53

[Amelynn]

I did not know the worksheet object code module, I found this new way of making macros (new to me) very interesting. Thank you very much again