controlSource ques

[Jeff Ciaccio]

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!!

 

[JLGWhiz]

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

 

[Jeff 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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Jeff Ciaccio]

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\)]

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

 

[Tim Zych]

It works for me in XL2000.

 

[Tim Zych]

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