Run-time error code when initiating userform

[rizzo93]

I'm trying to retrieve a cell's value to display in a userform text field.

Private Sub UserForm_Initialize()
    Call GetData
End Sub


Private Sub GetData()

    Dim r As Long
    
    r = Cells(ActiveCell.Row, 78).Value
    PMField.Text = Worksheets("GhostData").Cells(r, 132).Value
    
End Sub

When I run the code above, I get "Run-time error: '424' Object required"


What does that mean and how do I fix it, please?

 

[rizzo93]

I typed that in and I did get a drop-down menu showing PMField, but it had a "properties" icon instead of an "object" icon.

So I deleted that field in the userform and created another one with the same name. When I ran the code, the form popped-up showing the value I was targeting.

So problem solved! But there is just one more thing I could use your help with, if you don't mind....

As you can see in the code, I am using the active cell the user has clicked on (or maybe it's better to double-click?) and using that cell to find the value of another hidden cell on that same row. This is the "r".

The cells I want the user to click on are not to be edited, but they do need to be in the appropriate column. Do you have any suggestions?

Again, you've been awesome with helping me narrow that error down!

Sub PDCloseButton_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Call GetData
End Sub
Private Sub GetData()
    Dim r As Long
    
    r = Cells(ActiveCell.Row, 78).Value
    PMField.Text = Worksheets("GhostData").Cells(r, 132)
    
End Sub

 

[Fluff]

With your code it doesn't matter which column they click on as you are specifying the column in the code.

 

[rizzo93]

Ok. Does it matter if the cells they're clicking on are merged cells?



Row 17 has "cell" H17:Z17
Row 18 has "cell" H18:Z18
etc.


I found this code from another post, but I must be missing something because it's not executing properly:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Select Case Target.Value
    Case vbNullString, "No": Target.Value = "Yes"
    Case "Yes": Target.Value = "No"
End Select
End Sub

 

[Fluff]

Merged cells are a nightmare & I avoid them like the plague.
But I don't understand what this has to do your OP.

 

[rizzo93]

A while back I tried doing something with merged cells and had a problem with them, so I can relate.

The reason this code is related is the PMField (among many other fields) is going to be populated in the userform.


The value "r" is the crucial element to finding other data on the other worksheet being referenced.


Oh, and I can tell the user to click on a single cell instead of merged "cells".


If it were a button a user clicks on, I could call up the form easily. But I haven't done a double-click method before.

 

[Fluff]

Put this in the relevant sheet module

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
r = Cells(Target.Row, 78).Value
UserForm1.Show
End Sub

This at the top of a standard module (before any code)

Public r As Long

And the userform code is

Private Sub UserForm_Initialize()
    PMField.Text = Worksheets("GhostData").Cells(r, 132)
End Sub

 

[rizzo93]

OMG, Fluff! It's like a huge weight has been lifted!

Thank you so SO much! You are as awesome as you are patient.

 

[Fluff]

Glad to help & thanks for the feedback