VBA User Form Range to Variable- Syntax Issue

Question

I've created a VBA Userform in Excel, where the user selects several ranges. Within the user form, I have input validation through a series of If then MsgBox statements.

As part of this, I need to take the inputted range and use it as a variable.

Assuming that the range is Me.ActDurations, I tried to use this:

dim ActDur as range
set ActDur = Me.ActDurations

I've also tried:

set ActDur = Me.ActDurations.Value

And that doesn't work either. What is the proper syntax for this? Using the first type gives me a type mismatch error.

WGS · Accepted Answer · 2013-11-12 04:56:34Z

3

The .Value property of RefEdit returns a string. To use it as a range, you should use the string as a range name. Sample code below.

Dim address as String
Dim targetRange As Range

address = RefEdit1.Value 'String returned by the selected range using RefEdit.
Set targetRange = Range(address)

'Do some code here.

Modify as necessary for your code. ;)

answered Nov 12, 2013 at 4:56

WGS

14.2k5 gold badges50 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

chris neilsen Over a year ago

Worth noting, RefEdit value will include a sheet name, and may include a workbook name, in the form '[Workbook Name.extn]Worksheet Name'!RangeAddress. If it includes a workbook name, the answer will not work (works fine without workbook name)

WGS Over a year ago

@chris: This is something I'm wary of as well, which is why I personally don't go the RefEdit option if I can avoid it (too much maintenance over one simple value, tbh). However, what I provided should point OP to the right direction. Thanks for pointing out such an important note, though! +1

idalsin Over a year ago

Awesome, works for me! The assignment I'm completing requires me to use RefEdit, normally I'd avoid it like the plague. Thanks!

Collectives™ on Stack Overflow

VBA User Form Range to Variable- Syntax Issue

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related