1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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?]

Discussion in 'VBA Macros' started by ccsher77, Jun 10, 2013.

  1. ccsher77

    ccsher77 New Member

    Messages:
    15
    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
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,726
    Ccsher77


    Can you post your file for us to review?

    Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
  3. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    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!
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    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
  5. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    @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!
  6. ccsher77

    ccsher77 New Member

    Messages:
    15
    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
  7. Debraj

    Debraj Excel Ninja

    Messages:
    2,132
    Hi ccsher77!


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

    Can you please upload in some other site..


    Regards,

    Deb
  8. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    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
  9. Debraj

    Debraj Excel Ninja

    Messages:
    2,132
    Noted & Downloaded.. :)


    Thanks..
  10. Debraj

    Debraj Excel Ninja

    Messages:
    2,132
    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 (vb):
    Sub CommandButton1_Click()
    Sheets("Data").Cells(Sheets("Sheet3").ComboBox1.ListIndex + 2, 2) = Sheets("Sheet3").TextBox1
    End Sub
    [/pre]

    Regards,

    Deb
  11. ccsher77

    ccsher77 New Member

    Messages:
    15
    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
  12. Debraj

    Debraj Excel Ninja

    Messages:
    2,132
    Hi ccsher77!


    Glad that you like it.. and thanks for the valuable words.. :)
  13. ccsher77

    ccsher77 New Member

    Messages:
    15
    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
  14. ccsher77

    ccsher77 New Member

    Messages:
    15
    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?
  15. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, Craig!

    That sounds strange, any chance of uploading a sample file?

    Regards!
  16. ccsher77

    ccsher77 New Member

    Messages:
    15
    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.
  17. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    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!
  18. ccsher77

    ccsher77 New Member

    Messages:
    15
    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
  19. Debraj

    Debraj Excel Ninja

    Messages:
    2,132
    Hi Craig!


    Sorry for late response... :)


    * Can you please delete below code..

    [pre]
    Code (vb):
    '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

Share This Page