refedit won't work

[davegb]

I'm trying to use a refedit in a userform, but strange things are
happening. The user selects a range in the spreadsheet which is
supposed to be caputured by the refedit (reDataStrt) and saved as a
variable. I've tried doing it by having the range selected stored when
the OK button is clicked in the form code, and I've put the code to
save the range in the module.

The code in the module looks like this:

userf1021Mid.Show vbModeless
Set rColHdr = Range(reDataStrt.Value)

I was thinking the range could be set after the userform was hidden.
When I run it this way, I get a "variable not defined" error on
reDataStrt. So I moved it to the userform code like this:

Private Sub OKButton_Click()
Set rColHdr = Range(reDataStrt.Value)
userf1021Mid.Hide
End Sub

I thought this would cause the range to be set when the OK button was
clicked, just before the form is hidden and control goes back to the
module. But when I run it this way, I get a "object variable or with
block variable not set" error on rColHdr when I return to the module,
so the statement isn't working in the userform code.

I looked up how to use the refedit before I started this, and it didn't
seem this complicated in the examples I found in this NG. I'm confused.
Anyone have any idea how to capture the refedit range and use it?

Thanks again!

 

[Bob Phillips]

Dave,

The problem is starting the form modeless.

To quote help ... You cannot use a RefEdit control on a modeless user form.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[davegb]

Dave,

The problem is starting the form modeless.

To quote help ... You cannot use a RefEdit control on a modeless user form.

Thanks for your reply. I misunderstood the manual. It said you can't
access the spreadsheet while the userform was open unless it was
modeless. So I thought that to select the range where the data starts,
it would have to be modeless. Do I have it backwards? Does the userform
have to be Modal to access the spreadsheet while the userform is
present?

 

[Bob Phillips]

Yes, the userform has to displayed modal to use RefEdit.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[davegb]

Yes, the userform has to displayed modal to use RefEdit.

Thanks, Bob!

 

[davegb]

Yes, the userform has to displayed modal to use RefEdit.

Thanks, Bob. But the code still doesn't work. Any other ideas as to
why?

 

[davegb]

Thanks, Bob. But the code still doesn't work. Any other ideas as to
why?

I found it. I had declared rColHdr publicly as a range, but in the
userform code. I moved it to the module and that fixed the problem. I
have no idea why. Anyone know why a public variable has to be declared
in the module, not in the form?

 

[Bob Phillips]

If you declare it in the userform module, even as public, you cannot access
it outside the userform, unless you qualify it

Set userf1021Mid.rColHdr = Range(reDataStrt.Value)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[davegb]

If you declare it in the userform module, even as public, you cannot access
it outside the userform, unless you qualify it

Set userf1021Mid.rColHdr = Range(reDataStrt.Value)

I've decided I should have put all the code in the userform, so I moved
the first part to see if it will run. I also put the line above in, but
I'm still getting my error message that it's not a range. Any other
ideas?