I have a userform with textboxes in Excel 2000.

1) If you want to specify a certain cell in a certain worksheet, can you set
this in the properties, or must this be done with code? I tried sheet1!B1,
but it would not accept this.

2) Does the controlSource property work both ways? In other words, does a
change in that textBox change the cell AND does changing the cell also
change the textBox?

3) If data validation is set up in a cell, and the controlSource is set to
that cell, will the validation pass through to the user form? If not, will
something like this work?
Private Sub textBox1_Change()
Range [b1] = textBox1.value ' Or will it simply default to .value
if left off?
End Sub

Thanks!!

--
Jeff Ciaccio
Physics and AP Physics Teacher
Sprayberry High School; Marietta, GA
Blog: http://sprayberry.typepad.com/ciaccio

First two answers are yes. I don't quite get the third question.
But trying it, sometimes provides an answer.


"Jeff Ciaccio" wrote:

> I have a userform with textboxes in Excel 2000.
>
> 1) If you want to specify a certain cell in a certain worksheet, can you set
> this in the properties, or must this be done with code? I tried sheet1!B1,
> but it would not accept this.
>
> 2) Does the controlSource property work both ways? In other words, does a
> change in that textBox change the cell AND does changing the cell also
> change the textBox?
>
> 3) If data validation is set up in a cell, and the controlSource is set to
> that cell, will the validation pass through to the user form? If not, will
> something like this work?
> Private Sub textBox1_Change()
> Range [b1] = textBox1.value ' Or will it simply default to .value
> if left off?
> End Sub
>
> Thanks!!
>
> --
> Jeff Ciaccio
> Physics and AP Physics Teacher
> Sprayberry High School; Marietta, GA
> Blog: http://sprayberry.typepad.com/ciaccio
>
>

1) How do you specify a certain worksheet in the properties? I tried
sheet1!B1, worksheet(1).range("b1"), and a few others, but none of these
seemed to work.

3) I figured out that you have to use the beforeUpdate event. I manually
coded the validation, but it would be nice if somebody knows how to use the
validation that is set up in the cell. Here's the code I used should
anybody be interested.

Private Sub Time1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsNumeric(Time1.Text) Then
MsgBox ("Between 0 and +20")
Cancel = True
Else
Select Case Time1.Value
Case 0 To 20
Case Else
MsgBox ("Between 0 and +20")
Cancel = True
End Select
End If
End Sub


Thanks,
Jeff

> 1) How do you specify a certain worksheet in the properties? I tried
> sheet1!B1, worksheet(1).range("b1"), and a few others, but none of these
> seemed to work.

Sheet2!B1 works for me. Why do you think it isn't working?

Rick

When I try to set this directly in the controlSource property of the textBox
(in the properties window), I get an error
"Could not set the control source property. Invalid property value"


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:e%23FHx%(E-Mail Removed)...
>> 1) How do you specify a certain worksheet in the properties? I tried
>> sheet1!B1, worksheet(1).range("b1"), and a few others, but none of these
>> seemed to work.
>
> Sheet2!B1 works for me. Why do you think it isn't working?
>
> Rick

I should have mentioned that I am using XL2003... I see you are using
XL2000... I don't have XL2000 so perhaps the ability to do this did not
exist on your version (and earlier) of Excel. Hopefully, someone with XL2000
will come along and confirm this one way or the other.

Rick


"Jeff Ciaccio" <(E-Mail Removed)> wrote in message
news:64F8F79E-A208-45D3-B7E1-(E-Mail Removed)...
> When I try to set this directly in the controlSource property of the
> textBox (in the properties window), I get an error
> "Could not set the control source property. Invalid property value"
>
>
> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
> message news:e%23FHx%(E-Mail Removed)...
>>> 1) How do you specify a certain worksheet in the properties? I tried
>>> sheet1!B1, worksheet(1).range("b1"), and a few others, but none of these
>>> seemed to work.
>>
>> Sheet2!B1 works for me. Why do you think it isn't working?
>>
>> Rick
>

It works for me in XL2000.


--
Tim Zych
www.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
>I should have mentioned that I am using XL2003... I see you are using
>XL2000... I don't have XL2000 so perhaps the ability to do this did not
>exist on your version (and earlier) of Excel. Hopefully, someone with
>XL2000 will come along and confirm this one way or the other.
>
> Rick
>
>
> "Jeff Ciaccio" <(E-Mail Removed)> wrote in message
> news:64F8F79E-A208-45D3-B7E1-(E-Mail Removed)...
>> When I try to set this directly in the controlSource property of the
>> textBox (in the properties window), I get an error
>> "Could not set the control source property. Invalid property value"
>>
>>
>> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote
>> in message news:e%23FHx%(E-Mail Removed)...
>>>> 1) How do you specify a certain worksheet in the properties? I tried
>>>> sheet1!B1, worksheet(1).range("b1"), and a few others, but none of
>>>> these seemed to work.
>>>
>>> Sheet2!B1 works for me. Why do you think it isn't working?
>>>
>>> Rick
>>
>

If it's really called "Sheet1", what you are doing should work.

To validate, activate the sheet and in the immediate window type

?range("B1").Address(External:=True)

You should see something like

[Book2]Sheet1!$B$1

Validate everything after the Book2 part is the ControlSource

--
Tim Zych
www.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility



"Jeff Ciaccio" <(E-Mail Removed)> wrote in message
news:64F8F79E-A208-45D3-B7E1-(E-Mail Removed)...
> When I try to set this directly in the controlSource property of the
> textBox (in the properties window), I get an error
> "Could not set the control source property. Invalid property value"
>
>
> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
> message news:e%23FHx%(E-Mail Removed)...
>>> 1) How do you specify a certain worksheet in the properties? I tried
>>> sheet1!B1, worksheet(1).range("b1"), and a few others, but none of these
>>> seemed to work.
>>
>> Sheet2!B1 works for me. Why do you think it isn't working?
>>
>> Rick
>