Populate Range from string

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,713
I'm passing a Range to my Form via a Property
Code:
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)
Code:
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.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,027
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 1, 2009
Messages
1,713
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

Well-known Member
Joined
Jul 14, 2008
Messages
3,027
Office Version
  1. 365
Platform
  1. Windows
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.

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

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,713

ADVERTISEMENT

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

Well-known Member
Joined
Jul 14, 2008
Messages
3,027
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,115
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 14, 2008
Messages
3,027
Office Version
  1. 365
Platform
  1. Windows
Right, that's what I said.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,086
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
rng is a Range object, so all you should really need is:

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

Watch MrExcel Video

Forum statistics

Threads
1,122,737
Messages
5,597,826
Members
414,180
Latest member
Sir Khaya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top