Populate Range from string

[AlexanderBB]

I'm passing a Range to my Form via a Property

Dim Rng as Range
Public Property Let rRange(z as Range)
Set Rng= z
End Property

Then I attempt to populate that range (which is a single cell)

Private Sub cmdApply_Click()
Worksheets("Sheet1").Range(rng).Value = Me.txtTest
End Sub

I get error 1004 Application-defined or object-defined error
At this point rng.Address is $A$2 (which is correct) and txtTest is a valid string.

What am I doing wrong ? Thanks.

 

[lrobbo314]

Are you working with classes? Properties are a class thing and I don't see where you are using a class. I think you probably just want to either declare rng as a global variable, or pass the rng variable to your cmd_ApplyClick function. I don't think there is any way given what you've provided, that your cmd_Apply_Click function can read the rng variable.

 

[AlexanderBB]

Isn't a Userform a class?
I'm stepping though Sub cmdApply and I can debug.print rng.Address correctly.
Stepping also shows the Property being assigned.. You may well be right but I'm puzzled.
Yes, I could try other methods, but it'd be useful to know why this isn't working.

 

[lrobbo314]

Yeah, I'm not sure what you have going on. Classes are a different thing. Userform isn't a class. I'm surprised that you can read that variable, but whatever.

Maybe try this.

Private Sub cmdApply_Click()
Worksheets("Sheet1").Range(rng.address).Value = Me.txtTest
End Sub

 

[AlexanderBB]

Yep, that works! Nicely done... thanks.
There's much I don't know but I have found Property Gets and Lets work in Userforms and are very
straightforward. I load the Form first, assign the Property, then Show it.

 

[lrobbo314]

Cool. Might use that in the future. Glad that we got the solution to your problem. The 'Range' method expects a string, not a range object. Pretty trivial kind of mistake that I've made 1K times.

 

[Jaafar Tribak]

Yeah, I'm not sure what you have going on. Classes are a different thing. Userform isn't a class. I'm surprised that you can read that variable, but whatever.

Actually, a Userform is a Class with a user interface ... All that which applies to Classes also applies to userforms. Things like instanciation with the New keyword and defining Properties, Methods and Events.

 

[lrobbo314]

Right, that's what I said.

 

[RoryA]

rng is a Range object, so all you should really need is:

Private Sub cmdApply_Click()
rng.Value = Me.txtTest
End Sub