I have a user form that, based on the option button selected, a value needs
to be returned from a different cell in a separate worksheet. I get an out
of range error when I try the following code:

Sheets("Sample Selection").Select
ActiveSheet.Unprotect
Cells(2, 5) = Worksheets("SampleCalc").Cells(3, 45)

What am I doing wrong?

--
JP

subscript out of range errors means that the thing that caused the error doesn't
exist in the collection you're using.

Maybe it's a typo in the worksheet name (which line caused the error)?

or maybe the activeworkbook wasn't what you thought it was and so the worksheet
didn't exist in the real workbook that was active.

with activeworkbook.worksheets("sample selection")
.unprotect
.cells(2,5).value _
= activeworkbook.worksheets("samplecalc").cells(3,45).value
end with



JPCPA wrote:
>
> I have a user form that, based on the option button selected, a value needs
> to be returned from a different cell in a separate worksheet. I get an out
> of range error when I try the following code:
>
> Sheets("Sample Selection").Select
> ActiveSheet.Unprotect
> Cells(2, 5) = Worksheets("SampleCalc").Cells(3, 45)
>
> What am I doing wrong?
>
> --
> JP

--

Dave Peterson

Your code should work but no need to select

With Sheets("Sample Selection")
.Unprotect
.Cells(2,5) = Sheets("SampleCalc").Cells(3,45)
End With


--

Regards,
Nigel
(E-Mail Removed)



"JPCPA" <(E-Mail Removed)> wrote in message
news:3E2C7B3E-0EA5-4ADC-ACA9-(E-Mail Removed)...
>I have a user form that, based on the option button selected, a value needs
> to be returned from a different cell in a separate worksheet. I get an
> out
> of range error when I try the following code:
>
> Sheets("Sample Selection").Select
> ActiveSheet.Unprotect
> Cells(2, 5) = Worksheets("SampleCalc").Cells(3, 45)
>
> What am I doing wrong?
>
> --
> JP

Dave & Nigel,
Thanks for the responses -

Dave, I had the sheet spelled wrong (missing space). I also was referencing
the wrong cell - I did column,row, rather than row, column, hence the value
of the referenced cell was blank.

Nigel, Excellent thing to know. I've always selected the sheet first.
I'll note this for the future. Thank you.
--
JP


"Nigel" wrote:

> Your code should work but no need to select
>
> With Sheets("Sample Selection")
> .Unprotect
> .Cells(2,5) = Sheets("SampleCalc").Cells(3,45)
> End With
>
>
> --
>
> Regards,
> Nigel
> (E-Mail Removed)
>
>
>
> "JPCPA" <(E-Mail Removed)> wrote in message
> news:3E2C7B3E-0EA5-4ADC-ACA9-(E-Mail Removed)...
> >I have a user form that, based on the option button selected, a value needs
> > to be returned from a different cell in a separate worksheet. I get an
> > out
> > of range error when I try the following code:
> >
> > Sheets("Sample Selection").Select
> > ActiveSheet.Unprotect
> > Cells(2, 5) = Worksheets("SampleCalc").Cells(3, 45)
> >
> > What am I doing wrong?
> >
> > --
> > JP
>