Textbox property change via code - help required

[Kennyatwork]

Hello everyone

Another difficult one to explain!

A userform (containing text and combo boxes) is populated from an Excel
database.

Textbox2 has a number entered by the user then a commandbutton runs the
following code

(yes Tom, it looks remarkably like yours!)

ans = UserForm.TestBox2.Value
res = Application.Match(CLng(ans),Range("A":A3000"),0)
Range("A":A3000")(res).Activate
UserForm1.TextBox1.Text=ActiveCell.Offset(0,1).Value
UserForm1.ComboBox1.Value=ActiveCell.Offset(0,2).Value
<etc.>

Is there any way of saying the following

If ActiveCell.Offset(0,1).Value is <anything - numeric or text> then

set the textbox property as enabled=False else
textbox1 property=True

and so on

As you will gather I am trying to stop any editing of the database via the
userform

Thanks in advance

Kenny

 

[Tom Ogilvy]

If you don't link your cells to the controls, and it appears that you don't,
then the data can not be edited from the userform unless you write the code
to do the updating. There is no implicit connection between the controls
and the userform. Your code provides the link and as written it is "read
only"

this kind of code updates a cell

ActiveCell.Offset(0,2).Value = UserForm1.ComboBox1.Value

Yours goes all the other way, updating the control.

 

[Kennyatwork]

Sorry Tom, I've been battling with this on and off most of the day and I've
mixed up my code

My command button runs the following code

ans = UserForm1.TextBox2.Value
res = Application.Match(CLng(ans), Range("A2:A3000"), 0)
Range("A2:A3000")(res).Activate

With UserForm1

ActiveCell.Offset(0, 1).Value = .TextBox1.Text
ActiveCell.Offset(0, 2).Value = .ComboBox1.Value
ActiveCell.Offset(0, 3).Value = .ComboBox2.Value
ActiveCell.Offset(0, 4).Value = .ComboBox3.Value
ActiveCell.Offset(0, 5).Value = .ComboBox4.Value
ActiveCell.Offset(0, 6).Value = .ComboBox5.Value
ActiveCell.Offset(0, 7).Value = .ComboBox6.Value
ActiveCell.Offset(0, 8).Value = .ComboBox7.Value
ActiveCell.Offset(0, 9).Value = .TextBox3.Text
ActiveCell.Offset(0, 10).Value = .TextBox4.Text
ActiveCell.Offset(0, 11).Value = .TextBox5.Text
ActiveCell.Offset(0, 12).Value = .ComboBox8.Value

End With

now could I alter it to suit what I was trying to do?

Kenny

 

[Kennyatwork]

Hello

A note for anyone following this thread,

I never found how to change the property value via code - but I acheived
what I wanted by other means,
I was trying to stop the user editing text in the boxes however I now allow
them to do that but a simple If statement checks to see wether the target
cell contains text, If it does the contents of the text box is not used -
so the original data remains in tact!

If ActiveCell.Offset(0,1).Value ="" Then
ActiveCell.Offset(0,1).Value = UserForm1.TextBox1.Value
End If

Kenny