VBA Syntax: Runtime Error 424 - Object required

Question

This might have been asked already, but I still need to ask, since I am fairly new to VBA and programming as a whole, I can't figure out what i am doing wrong. I have a userform, that fetches data from a textbox (in this case a number), and searches it in my table. Then, from that Range, I am making steps to retrieve the data connected to this number:

Defined Variables:

Sub UserForm_Initialize()
Call CloseButtonSettings(Me, False)
Dim selected_col As Long
Dim PZ_ID As Single, KD_ID As Single, Customer_Combination As String, Ship_ID As String, Author_ID As String, _
Art_Lager As Single, Art_Bestell As Single, DTPicker1 As Date, Calc_Time As Single, Time1 As Single, _
Time2 As Single, Time3 As Single, Time_Special As Single, Time_Total As Single, Notes_Buero As String, Notes_Lager As Single
End Sub

The Search + Putting the data into the textboxes

  Private Sub CommandButton1_Click()
    Dim PZ_RNG As Range
    Dim strSearch As String

    strSearch = Packzettelinfo.PZ_ID
    Set PZ_RNG = Range("B:B").Find(strSearch, , xlValues, xlWhole)
    If Not PZ_RNG Is Nothing Then
    Else
        MsgBox "Packzettel Nr. " & strSearch & " konnte nicht gefunden werden (Fehler #001)", vbOKOnly
        Packzettelinfo.PZ_ID.SetFocus
    End If
    Range("E1") = PZ_RNG.Row 'Saving for later

Packzettelinfo.KD_ID = Cells(PZ_RNG.Row, PZ_RNG.Column + 1)
Packzettelinfo.Customer_Combination = Cells(PZ_RNG.Row, PZ_RNG.Column + 2)
Packzettelinfo.Ship_ID = Cells(PZ_RNG.Row, PZ_RNG.Column + 3)
Packzettelinfo.Author_ID = Cells(PZ_RNG.Row, PZ_RNG.Column + 4)
Packzettelinfo.Art_Lager = Cells(PZ_RNG.Row, PZ_RNG.Column + 5)
Packzettelinfo.Art_Bestell = Cells(PZ_RNG.Row, PZ_RNG.Column + 6)
Packzettelinfo.DTPicker1 = Cells(PZ_RNG.Row, PZ_RNG.Column + 7)
Packzettelinfo.Calc_Time = Cells(PZ_RNG.Row, PZ_RNG.Column + 8)
Packzettelinfo.Time1 = Cells(PZ_RNG.Row, PZ_RNG.Column + 10)
Packzettelinfo.Time2 = Cells(PZ_RNG.Row, PZ_RNG.Column + 11)
Packzettelinfo.Time3 = Cells(PZ_RNG.Row, PZ_RNG.Column + 12)
Packzettelinfo.Time_Special = Cells(PZ_RNG.Row, PZ_RNG.Column + 13)
Packzettelinfo.Time_Total = Cells(PZ_RNG.Row, PZ_RNG.Column + 14)
Packzettelinfo.Notes_Buero = Cells(PZ_RNG.Row, PZ_RNG.Column + 15)
Packzettelinfo.Notes_Lager = Cells(PZ_RNG.Row, PZ_RNG.Column + 16)
End Sub

This works fine!

But when i try to save the textboxes (when the contents have been changed) it says "Runtime Error 424":

Private Sub CB_PZ_save_edit_Click()
Cells(PZ_RNG.Row, PZ_RNG.Column + 1) = Packzettelinfo.KD_ID
Cells(PZ_RNG.Row, PZ_RNG.Column + 2) = Packzettelinfo.Customer_Combination
Cells(PZ_RNG.Row, PZ_RNG.Column + 3) = Packzettelinfo.Ship_ID
Cells(PZ_RNG.Row, PZ_RNG.Column + 4) = Packzettelinfo.Author_ID
Cells(PZ_RNG.Row, PZ_RNG.Column + 5) = Packzettelinfo.Art_Lager
Cells(PZ_RNG.Row, PZ_RNG.Column + 6) = Packzettelinfo.Art_Bestell
Cells(PZ_RNG.Row, PZ_RNG.Column + 7) = Packzettelinfo.DTPicker1
Cells(PZ_RNG.Row, PZ_RNG.Column + 8) = Packzettelinfo.Calc_Time
Cells(PZ_RNG.Row, PZ_RNG.Column + 10) = Packzettelinfo.Time1
Cells(PZ_RNG.Row, PZ_RNG.Column + 11) = Packzettelinfo.Time2
Cells(PZ_RNG.Row, PZ_RNG.Column + 12) = Packzettelinfo.Time3
Cells(PZ_RNG.Row, PZ_RNG.Column + 13) = Packzettelinfo.Time_Special
Cells(PZ_RNG.Row, PZ_RNG.Column + 14) = Packzettelinfo.Time_Total
Cells(PZ_RNG.Row, PZ_RNG.Column + 15) = Packzettelinfo.Notes_Buero
Cells(PZ_RNG.Row, PZ_RNG.Column + 16) = Packzettelinfo.Notes_Lager
End Sub

Again, since I am new, this might be a stupid approach for what i want to do but oh well...

@braX Packzettelinfo is my Userform where the Textboxes (KD_ID etc.) are — Leon S, May 28 '20 at 08:52
Its starts already on the last part at ```Private Sub CB_PZ_save_edit_Click() Cells(PZ_RNG.Row, PZ_RNG.Column + 1) = Packzettelinfo.KD_ID``` — Leon S, May 28 '20 at 08:54
`PZ_RNG` Range is in second procedure, cannot access in third. — Naresh, May 28 '20 at 08:55
Then check the values of each of those things to make sure they are what you expect them to be... for example, what is the value of `PZ_RNG.Row`? Then check the others... — braX, May 28 '20 at 08:55
If that gives you an error, what is the value of `PZ_RNG` ? etc... — braX, May 28 '20 at 08:56
@NareshBhople How can I make it that it is accessable in the third one? — Leon S, May 28 '20 at 09:01
I would recommend make a global array for that. So , all your Packzettelinfo values will go into the array in the second procedure and you can access them in the third. Though I am not understanding those are the same cells then what is the difference. with Packzettelinfo values — Naresh, May 28 '20 at 09:02
Please can you provide documentation on that? I am a total noob.. lol — Leon S, May 28 '20 at 09:03
@NareshBhople I want to put in a value i want to search for, list the according data and change the data, save it in the same cells... — Leon S, May 28 '20 at 09:08
Ok.. I can see you are using Range("E1") to store PZ_RNG.Row in the second procedure. If you can change it to `Range("E1") = PZ_RNG.Address` and access it in the third procedure then `Cells(Range(Range("E1")).Row, Range(Range("E1")).Column + 1) = Packzettelinfo.KD_ID` should work in the third procedure. — Naresh, May 28 '20 at 09:18
All the variables defined in `UserForm_Initialize()` are forgotten once the form is initialized... When you use them in `CB_PZ_save_edit_Click()` they will all be implicitly type `Variant` - just FYI. You might wish to declare them in each module you want to use them. — Samuel Everson, May 28 '20 at 09:20
It looks like [scope](https://stackoverflow.com/a/2726329/9663006) is something important here. That link will help with global variables (if they are in fact needed). — Samuel Everson, May 28 '20 at 09:29
@NareshBhople I just tried yours; it doesn't spit any errors anymore, and your solution seems logical for me; works like a charm, thank you very much! (I did not know about variable.address, thats why i didn't try that already :D! — Leon S, May 28 '20 at 09:33
Leon apology for overlooking. I thought `KD_ID` is text box in userform `Packzettelinfo`. So, being KD_ID a variable, I must agree with @SamuelEverson. — Naresh, May 28 '20 at 09:33
@SamuelEverson Thats a very good source, thanks alot. I knew about Global/Public, but did never use it since there was no vba code i did with more than one module/sub haha — Leon S, May 28 '20 at 09:35
@NareshBhople Yes I do! The value in the spreadsheet changed. Also, it is a textbox.. I don't need to declare them then? (bc textbook is always text format right? I need the values since i am probably calculating, but this may also be in excel itself...) — Leon S, May 28 '20 at 09:36
So you don't even have to define them as variables like `Dim PZ_ID As Single, KD_ID As Single` in `UserForm_Initialize`. You can access them directly in any procedure like `Packzettelinfo.KD_ID` ... Cheers !! — Naresh, May 28 '20 at 09:51

score 1 · Accepted Answer · answered May 28 '20 at 10:28

Let's take a step back and look at what we want to do.

Get some data from a range on a workbook and populate it into a userform textbox.
Edit the data on the userform.
Update the new data back to the worksheet.

Now we can focus our efforts into these 3 steps.

But before we get into that, you'd benefit from understanding scope in VBA.

Now your code isn't the most descriptive (at least not in English) so I'm going to make a few assumptions if it's not clarified in the questions comments.

The below is written on a new userform with a textbox, label and 2 commandbuttons, all with default names (screenshots below).

Step 1:

Your method for getting the data and putting it into a textbox is good. To avoid ambiguity it's best to always explicitly qualify our statements so the code looks at the right workbook/worksheet or userform etc.

Private Sub CommandButton1_Click()

    Dim TargetCell As Range
    Dim SearchString As String
    SearchString = ThisWorkbook.Sheets("Sheet1").Range("A1").Value 'Only "Range("A1").Value" implies ActiveSheet which may give false results.

    Set TargetCell = ThisWorkbook.Sheets("Sheet1").Range("B:B").Find(SearchString, , xlValues, xlWhole)

    If Not TargetCell Is Nothing Then
        'TargetRange Is Something, do the thing
        'You could assign the values to the textbox here rather than outside of the If statement
        'Or you could change it to If TargetCell Is Nothing Then - And only execute the code in the Else section below
    Else
        MsgBox "Packzettel Nr. " & strSearch & " konnte nicht gefunden werden (Fehler #001)", vbOKOnly
        UserForm1.TextBox1.SetFocus
        Exit Sub    'This stops the rest of the code from running - I'm assuming the above is an error that nothing was found.
    End If

    ThisWorkbook.Sheets("Sheet1").Range("E1") = TargetCell.Address 'Saving for later

    UserForm1.TextBox1.Value = ThisWorkbook.Sheets("Sheet1").Cells(TargetCell.Row, TargetCell.Column).Offset(0, 1).Value

End Sub

The only real changes I've made to the code are being explicit with where the Range or Cells are or that we want to access the Value property of that object.

That and using the Offset property rather than + 1 etc. for the columns.

Step 2:

The user changes the data as required - nothing for us to do programming wise.

You could add some steps here for data validation etc.

Step 3:

Writing the data back to the sheet again, you have written fairly well. Again I'd ensure to explicitly qualify objects like Range and Cells with the relevant workbook/worksheet and what property we want to access such as Value.

This time I'l assign the workbook/sheet to a variable.

Private Sub CommandButton2_Click()

    Dim TargetSheet As Worksheet
    Set TargetSheet = ThisWorkbook.Sheets("Sheet1")
    Dim TargetCellAddress As String

    With TargetSheet
        TargetCellAddress = .Range("E1").Value  'Recall our saved cell address from the Commandbutton1 click
        .Range(TargetCellAddress).Offset(0, 1).Value = UserForm1.TextBox1.Value
        .Range("E1").ClearContents  'Remove TargetCellAddress value from the sheet
    End With

End Sub

Here is basically the same as above but in reverse, though using the saved Range.Address property from before (In E1).

Note you can encapsulate your statements within a With Statement like I did with the TargetSheet that way you can access it's objects, properties and methods without having to qualify it each time - though each statement does start with a .

To summarise;

no variables needed to be declared for any of the textbox values, just assign them directly from/to the userform/worksheet and visa versa.
Qualify your objects etc. such as Range or Cells and explicitly reference which property you want to use, i.e. Value (some default to this but not all!).

Here are some screenshots with each click.

UserForm first opened:

Commandbutton1 clicked:

TextBox1 edited:

Commandbutton2 clicked:

Okay, first I have to thank you very much for all this explaining. That helped me alot actually. This is the first "real answer" for me that helped; again, thanks. The step with the offset was nice. I was thinking about using a for loop to get through all values but that wont work bc of the different textbox names (if it was tb1-tb15 it could be solved maybe?). This reading - saving part will come handy in another part of my vba project where i want to add a new line where everything is blank, so this will work for that as well. I really appreciate your time and dedication! Cheers. — Leon S, May 28 '20 at 10:46
_Now your code isn't the most descriptive (at least not in English) so I'm going to make a few assumptions if it's not clarified in the questions comments._ - It's German, and your assumtions were very accurate :) — Leon S, May 28 '20 at 10:47
@LeonS well although the comments were helpful on your question, the point of the site is question *and* answer - And I had some time on my hands and like to explain things! - You can create a loop and check for each checkbox, then access the value - see [this answer](https://stackoverflow.com/a/43331780/9663006) for a simple example of how to do this. It can be lengthy to write the code if you have other textbox you want to ignore though so keep that in mind too. If you get stuck, ask another question! Happy coding! :) — Samuel Everson, May 28 '20 at 12:55

VBA Syntax: Runtime Error 424 - Object required

1 Answers1

Step 1:

Step 2:

Step 3: