Textbox & Object problem

[jasondu]

Hey all ..

pulling my hair out on this one beause vba is so confusing! (sorry I'm new to VBA)

First of all .. do you even need to dim textbox1 as textbox?

Second .. why does it give me an error saying object required .. I though textbox1 is an object?

third .. how do i fix this? I just want to make the value of whatever in textbox1 (and 2) a variable that I can use later on in my code .. yet i have no idea why it keeps giving me trouble.

Please Login or Register  to view this content.

help!


*ALSO: can anyone explain to me what textbox1_change() is .. why are there drop downs for the main worksheet, but not for the module? and why everytime i create a textbox the _change() thing comes up?

Thanks
Jason

[tkowal]

There are two different types of Texboxes in excel Form and Active X. Sending a sample spreadsheet would assist us in help you with your problem.

Typically, unless you are dynamically instancing a textbox you do not have to declare it "Dim it". Where is this code placed? On a worksheet, in a module, in thisworkbook?

You do not need to "Set" your variable to the Textbox... simply for example: quarter = TextBox1.Text

Attached is an example of Assignment: AssignVar.xlsm

For your other questions this link is a good starting point.

http://www.cpearson.com/excel/Events.aspx

[JohnM3]

Hi
Not sure I can help because I don't know where your textbox is.

Assume:
(a) ActiveX Textbox on Worksheet

Just stick this code in and F8 thru it

Please Login or Register  to view this content.

Assume:
(b) Textbox on User form

Please Login or Register  to view this content.

[jasondu]

Hey both Tkowal and JohnM3 .. thanks for both your replies!

@Tkowal: my textbox is located physically on the spreadsheet .. so, on sheet1 I have a box that I used the design tool to draw and code is located in module1

Also if I don't set my variables to textbox, it will say "object variable or block variable not set"

What do you mean dynamically instancing?

@ JohnM3: The wierd thing is last time I placed a textbox(using design tool) on another workbook I didn't have to write all that coding as in your first code .. didn't have to declare anything as object or OLEobject .. also why are you using oTextBoxA/B/C?

Thanks alot for your help guys, I really appreciate you helping out a novice like myself!

Jason

[JohnM3]

Jason

You missed my point - which is -

(1) If you DEFINE a variable as an OLEOBJECT variable - code works
(2) If you DEFINE a variable as an OBJECT variable - code works
(3) If you DEFINE a variable as a TextBox variable - code fails

-- ALL OF THE ABOVE -- are objects.

But if you an ActiveX Textbox OBJECT on a worksheet - and use a variable to
reference it - ONLY

(1) If you DEFINE a variable as an OLEOBJECT variable - code works
(2) If you DEFINE a variable as an OBJECT variable - code works

will work - (as far as I know)

IOW - an OLEOBJECT TEXTBOX object is NOT the same as a TEXTBOX object

HTH
John

[tkowal]

Dynamically instancing, an example is when a text box is not on a worksheet and you are adding one soley by code.... then you would instance a copy of a text box object in memory and write it to a sheet. You would then use a "Set". Otherwise it is not necessary... I attached a working example to my post that assigns the values of two text boxes to two variables, Quarterly and Year.... the test I used was to show a message box using both variables (that were assigned not using set) from the two boxes.

The working code in the example that shows the messagbox:

Please Login or Register  to view this content.

------
Just Read Johns thread's.... Johns threads is showing instancing....

You use set when assigning to an Object not a variable!

Textbox1.Text is a property of the Textbox object thus can be assign to a variable without the set.

[jasondu]

Okay tkowal, what you say makes sense ... however when I try doing this code (ignore all others except textbox1) it gives me Run time error 91: Object Variable or With block variable not set message. what am i still doing wrong?

Please Login or Register  to view this content.

@ JohnM3 It says unable to get OLE properties of Worksheet class

Please Login or Register  to view this content.

[tkowal]

I am not a good teacher... but I will try and explain:

In your code the statement "Dim TextBox1 as textbox, TextBox2 as textbox"

This creates two "Object variables" for lack of the proper term. These have not been instanced yet so they are really null valued objects in memory. These do not point to the two boxes on your spreadsheet but are only in memory. To instance them you would need to do something like Dim Textbox1 as NEW Textbox. Doing this would give you an object Textbox1 (notice I said now an object not variable) that was created as a perfect copy of a generic Textbox object. These still would not point to the TextBox1 on your spreadsheet.

Textbox1 contains methods and properties so it is not one variable. Example properties include the Text it contains (Textbox1.Text) there are lots of other properties such as Font, Enabled, Shadow,Width, Wordwrap ....... It also contains events such as Change, LostFocus, Click .... this is why you cannot simply assign this to a variable. However, the properties can be assigned to variables.

Your problem wanted the text in the textboxes on the spreadsheet to be assigned to a variable. The textbox1 you dim 'd in code is some unset object and it has no relationship to the Textbox1 on your spreadsheet so it is unnecessary and should be removed from your code. If you looked at the example spreadsheet I previously attached, you will notice that I neither Dim'd the Textbox1 nor did I have to use the set statement. I just simply assigned the text property of the Textbox that was on the spreadsheet to the variable.

Upload a sample workbook that contains your problem... that would assist us and assist you in the understanding of any corrections to the code made.

Otherwise we will be going around in circles...

[JohnM3]

Jason

(a) Go to your SHEET2 - delete ALL controls on it
(a1) Make sure you are still on SHEET2
(b) Go to Developer in the Ribbon
(c) Click on Design Mode - so that it is depressed - (orange background)
(d) Click on Insert (next to Design Mode)
(e) Put two ACTIVEX textbox's on the Worksheet SHEET2

Stick this code in a module

Please Login or Register  to view this content.

(f) Go back to SHEET2 and CLICK DESIGN MODE so that it is NOT depressed
(g) Type some text in each text box on SHEET2
(h) Goto the module where the code above is
(i) F8 thru the code
(j) All things equal - the code will ONLY fail - if the 2 ActiveX textboxes are NOT
named Textbox1 and Textbox2
(k) You can see the name of the Textbox by Right Click on the textbox -> properties -> name
BUT - the Design Mode ribbon thingie has to be depressed (orange background)

HTH
John

[jasondu]

@Tkowal: So what you are saying is I can't declare Textbox beause its an object? So if I wanted to assign *variable = textbox1.text* that I would just have to not dim anything?

Also I cannot open your file correctly since it is read only and also in an older version .. if its not too much trouble for you can you make it into a 2003 compatible version?

@JohnM3: I will try your code, but I already created the activeX textboxes using ribbon from before .. do I have to erase and recreate again?

Also why is it from before when I took the exact process that you showed that I did not have to stick the code you mentioned (below) into module?

Please Login or Register  to view this content.

[JohnM3]

Jason

My bad.

**All** the code I sent you was (implied) in a module. A GENERAL MODULE.

Lets try to crawl before we walk.

(a) Delete Sheet2.
(b) Add a new Sheet and name it Sheet2.
(c) Then follow the previous instructions.
(d) Lastly, the code COULD go into the Sheet2 module - but - I don't know what *else*
is going on - so lets keep it simple for me.

If we can get past the steps I outlined - then I am going to ASK you to change
the variable types - but not yet.

HTH
John

[tkowal]

I converted the sample I sent to you into 2003 format. Hopefully you can try out the file now.

John and I are attacking your problem from two different angles... so I will back out for now to lessen any confusion.

AssignVar.xls

[JosephP]

1. no you don't need to declare variables since the textboxes are actual existing objects like the sheet they are on. (you could declare variables if you want to, but it serves no real purpose here)
2. if they are activex textboxes you should declare them as msforms.textbox and not just textbox (which is a different object)
3. as already said, you don't use Set when referring to data types rather than objects. so you would use Set if assigning the textbox itself to a variable but you don't when assigning its Text property, which is just a String.

in case it helps.

[jasondu]

Thanks JohnM3, Tkowal, and JosephP .. I have found a "work around" through the tips all of you mentioned and if anyone can explain why / if even this is valid coding I would appreciate it.

First I deleted sheet2 and created a new sheet, using ribbon drew out the textboxes again as JohnM3 suggested.

Then I removed the declarations and "set" as Tkowal and JosephP said, and instead added Sheets("Update") in front of textbox1.text .. and somehow that did the trick. Can anyone tell me if this is valid coding / acceptable practice or just a bad fix?

Please Login or Register  to view this content.

But in any case thanks for all your help!

[JosephP]

that is perfectly valid. :-)

[JohnM3]

Looks great!