refedit won't work
 

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!

refedit won't work
 

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" wrote in message
ps.com...
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!

refedit won't work
 

Bob Phillips wrote:
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

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?


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

"davegb" wrote in message
ps.com...
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!

refedit won't work
 

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

--
HTH

Bob Phillips

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

"davegb" wrote in message
ps.com...

Bob Phillips wrote:
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

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?


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

"davegb" wrote in message
ps.com...
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!

refedit won't work
 

Bob Phillips wrote:
Yes, the userform has to displayed modal to use RefEdit.

--
HTH

Bob Phillips

Thanks, Bob!


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

"davegb" wrote in message
ps.com...

Bob Phillips wrote:
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

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?


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

"davegb" wrote in message
ps.com...
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!

refedit won't work
 

Bob Phillips wrote:
Yes, the userform has to displayed modal to use RefEdit.

--
HTH

Bob Phillips

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


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

"davegb" wrote in message
ps.com...

Bob Phillips wrote:
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

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?


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

"davegb" wrote in message
ps.com...
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!

refedit won't work
 

davegb wrote:
Bob Phillips wrote:
Yes, the userform has to displayed modal to use RefEdit.

--
HTH

Bob Phillips

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?


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

"davegb" wrote in message
ps.com...

Bob Phillips wrote:
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

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?


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

"davegb" wrote in message
ps.com...
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!

refedit won't work
 

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" wrote in message
ps.com...

davegb wrote:
Bob Phillips wrote:
Yes, the userform has to displayed modal to use RefEdit.

--
HTH

Bob Phillips

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?


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

"davegb" wrote in message
ps.com...

Bob Phillips wrote:
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

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?


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

"davegb" wrote in message
ps.com...
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!

refedit won't work
 

Bob Phillips wrote:
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

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?


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

"davegb" wrote in message
ps.com...

davegb wrote:
Bob Phillips wrote:
Yes, the userform has to displayed modal to use RefEdit.

--
HTH

Bob Phillips

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?


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

"davegb" wrote in message
ps.com...

Bob Phillips wrote:
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

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?


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

"davegb" wrote in message
ps.com...
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!