• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

RT error 424 Object required - when trying to modify textbox value [SOLVED?]

ccsher77

New Member
Hi All,


I have recieved som invaluable help from this forum recently so thanks to everyone wha has taken the time to reply.


Although I am slowly getting to gips with the basics I have yet again come up against an issue which has me slightly stumped.


I am using a worksheet as a data entry tool (rather than a userform as it will be used by numerous ops and I have experienced issues regarding display sizes in the past)

The data entry worksheet contains Textboxes into which the Op enters the required information.

This (by means of a command button) is then copied to a data sheet.

I then Have 3rd(summary) sheet which is extremeley similar to the data entry sheet but is used to display the information, the op selects a given number from a Combobox which is tied to a named range and the textboxes display the data based on that selection.


I have this all working perfectly.


My question is if I wanted to allow the user to change the value in a specific textbox on the summary sheet, how would i then then ensure that the updated changes are saved to the correct cell on the worksheet when a command button is pressed.?


I have manged do this in the past when using userforms by using the following code:


Private Sub CommandButton1_Click()

Range("MyRange").Cells(ComboBox1.ListIndex + 1, 2) = TextBox1.Value

End Sub


But When I try and utilise this code for a worksheet based textbox i get a Run time error 424 Object required.

I presumed I am defining the object above but obviousley not.


Can anyone help?


Thanks in advance
 
Ccsher77


Can you post your file for us to review?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi, ccsher77!

Would you check if you defined the controls as ActiveX controls instead of form controls? From the Programmer tab, Controls group, click on Design Mode icon; then right click on each control and the Properties window should pop up and not the control format contextual menu for shapes and assign macros.

Regards!
 
Hi ,


You are getting the error because the reference to TextBox1 is not fully qualified ; instead of using just :


TextBox1.Value


use


Worksheets("Sheet1").TextBox1.Value


replacing the name Sheet1 by whatever is the name of the sheet where your textbox is placed.


Narayan
 
@NARAYANK991

Hi!

The code works fine just as it was posted, no qualifying needed if as it seems to be the code for the click event of a command button control, ergo place in the same worksheet object code section. Check this file:

https://dl.dropboxusercontent.com/u/60558749/Run%20time%20error%20424%20Object%20required%20Error%20when%20trying%20to%20modify%20textbox%20value%20%28for%20ccsher77%20at%20chandoo.org%29.xlsm

Regards!
 
Hi,


Thanks for all your responses, I guess maybe I have confused the matter slightly.


What I am attempting to do is on sheet 3 I have a combobox which populates the textboxes based on the selection.

The data that is used to populate the textboxes is stored on the worksheet named Data.


This works well, however what I want to do is if the operator modifies the text in any of the boxes then presses command button 1 the modified text is then updated to th relevant cell in the data sheet.


I have uploaded a copy of the workbook to the following:


https://skydrive.live.com/redir.aspx?cid=f5966aed44b736e9&page=view&resid=F5966AED44B736E9!154&parid=F5966AED44B736E9!137&authkey=!AvNnEVQE9UxWN5w&Bpub=SDX.SkyDrive&Bsrc=Share


Hopefully once you see the file it will make more sense
 
Hi ccsher77!


I think.. SkyDrive not able to store your Drawing Object & VBA project..

Can you please upload in some other site..


Regards,

Deb
 
Hi Deb ,


Please note that you will see on the screen in the top left hand corner , the file name ; click on this and save to your computer.


Narayan
 
Hi ccsher77!


Your technique is perfect..


Just you need to decide.. which object is reside in which Object..

i.e

Need to updated cells are reside in Sheets("Data")

Combobox & Textboxes are resided in Sheets("Sheet3")


So when your are running above error code.. it tried to search all object in current sheet..


Please go through the below code and adapt it accordingly for all other TextBoxes..

[pre]
Code:
Sub CommandButton1_Click()
Sheets("Data").Cells(Sheets("Sheet3").ComboBox1.ListIndex + 2, 2) = Sheets("Sheet3").TextBox1
End Sub
[/pre]

Regards,

Deb
 
Hi Deb,


Thank you SOOOO Much!!!this has been sending me insane for days!


I really appreciate yours and all the rest of the Guys help on this forum


it has become an invaluable source of help and information


So Once again thank you very much indeed & have a great day!!


Craig
 
Hi Guys,


Sorry to resurrect an old thread!


Debs code worked perfectley for textboxes so attmepted to amend it for specific cells (see below)


Sheets("Data").Cells(Sheets("Sheet3").ComboBox1.ListIndex + 2, 6) = Sheets("Sheet3").Range("D21").Value


However whilst the macro doesnt error it also doesnt replace the cell in the dat sheet with the amendment in cell D21.


Any ideas?


Thanks in advance
 
Hi Deb,


Apologies for resubmitting an old thread.


I just noticed that the code you generously supplied below, only works for textbox 1, what i mean is if I add another line


Sub CommandButton1_Click()

Sheets("Data").Cells(Sheets("Sheet3").ComboBox1.ListIndex + 2, 2) = Sheets("Sheet3").TextBox1

End Sub


only works for textbox 1, what i mean is if I add another line for a second textbox the data sheet is only updated with textbox 1 change.

I have tried the following code but I cannot get it to work


Sub CommandButton1_Click()

Sheets("Data").Cells(Sheets("Sheet3").ComboBox1.ListIndex + 2, 2) = Sheets("Sheet3").TextBox1

Sheets("Data").Cells(Sheets("Sheet3").ComboBox1.ListIndex + 2, 3) = Sheets("Sheet3").TextBox2

End Sub


Can you help?
 
Sirjb7,

There is a link to a copy of the file further up this thread.


Any help you can offer would berate fully received


What I think is happening is that once the code replaces the cell it retires the change event before it can run the second line.
 
Hi, Craig!


Sorry but I don't understand how does "Button 1" should work.


I found this in your uploaded file:


a) Dynamic range MyRange2 has this definition:

=DESREF(Data!$A$2;0;0;CONTARA(Data!$A:$A);1) -----> in english: =OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A),1)

should it have this?:

=DESREF(Data!$A$2;0;0;CONTARA(Data!$A:$A)-1;1) -----> in english: =OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A)-1,1)

As now it's having a blank entry line at the end (check this from the name manager and clicking on the RefersTo textbox.


b) For worksheet Data shape "Button 1" the called macro "CommandButton1_Click" from module Module1 had this non-working code:

Cells(Sheets("Sheet3").ComboBox1.ListIndex + 2, 2) = Sheets("Sheet3").TextBox1

which I changed after reading previous posts to:

Sheets("Data").Cells(Sheets("Sheet3").ComboBox1.ListIndex + 2, 2) = Sheets("Sheet3").TextBox1

and entering values in D21 they were updated in column B (and later with your update F) of the related row at worksheet Data.


c) Finally I placed your last code in CommandButton1_Click procedure:

Sheets("Data").Cells(Sheets("Sheet3").ComboBox1.ListIndex + 2, 2) = Sheets("Sheet3").TextBox1

Sheets("Data").Cells(Sheets("Sheet3").ComboBox1.ListIndex + 2, 3) = Sheets("Sheet3").TextBox2

and I got columns B and C of worksheet data correctly updated.


Sorry but I can't reproduce your issue and the code works fine as Debraj Roy told you earlier.


Regards!
 
Hi SirJB7,


Apologies for the delay, and thanks for you help so far.


The sheet was probably a little confusing as it is work in progress.


I have uploaded a modified version here:


https://skydrive.live.com/redir?resid=F5966AED44B736E9!159&authkey=!AIBmocaG2dwTpFE


As you will see on Sheet 1 I have 2 textboxes and a Command button, the code copies the textbox entries to onto the "Data" sheet into columns B & C sheet on the next empty row (there is a seperate code which indexes the number in column A by 1 everytime a new entry is made. - This all Works as expected.


In Sheet 3 there is a Combobox, 2 Text Boxes and A command button.

The textboxes are populated with information from the Data Sheet based on the selction made in the combobox. - This works perfectly as well.


However What I want is the ability for the user to amend the info displayed in the textboxes and then when the command button is pressed this info is copied back into the correct cells in the data sheet. As you will see this works perfectly well for textbox 1 but the entry in textbox 2 does not copy back to the data sheet.


I really am stumped as to why I cant get this to work


Any help is as always very much appreciated
 
Hi Craig!


Sorry for late response... :)


* Can you please delete below code..

[pre]
Code:
'Private Sub WorkSheet_Activate()
'    Worksheets("Sheet3").OLEObjects("ComboBox1").ListFillRange = "MyRange2"
'    ComboBox1.ListIndex = 0
'End Sub
[/pre]
Lemme elaborate..

* You Clicked CommandButton1.

* TextBox1's Value picked up..

* Chekced in Sheet 2 for correct location.. and make changes there.

* again you came back to Sheet 3..

* here its before checking text box 2.. it first run worksheet_activate event.

* In worksheet_Activate event.. you have set to reset ComboBox1 to 1.

* which couse ComboBox_change.

* at ComboBox_change, you have TextBox(1 & 2) to value according to ComboBox1..

* So Now Text Box 2 is New Value which is 1, of ComboBox..


* then It pick TextBox2' Value..


and you know the rest..

Just delete above code.. everything will be fine..


Regards,

Deb
 
Back
Top