current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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. ;)

share|improve this answer
2  
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) –  chris neilsen Nov 12 '13 at 5:04
    
@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 –  Nanashi Nov 12 '13 at 5:10
    
Awesome, works for me! The assignment I'm completing requires me to use RefEdit, normally I'd avoid it like the plague. Thanks! –  idalsin Nov 12 '13 at 5:18
    
@idalsin: Very glad to help. Thanks for accepting. –  Nanashi Nov 12 '13 at 5:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.