Programming problem

[Imran]

I want to fixed the cell to enter the data in specific range 5 to 100 or

100. basically i want to lock the worksheet cell and enter data in the cell

within the specefic range that i will provide .

 

[Gary''s Student]

Use Data Validation.

 

[Jacob Skaria]

Hi Imran

1. Use Data validtion>Allow Whole number> Select the minimum and maximum and
from error alert tab "You can place a customized message"

2. If you want to have this programatically use the worksheet change event.
Chanage the range to suit your requirement.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCol As Integer
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
If Target.Value < 5 Or Target.Value > 100 Then
MsgBox " Invalid Entry": Target.Value = ""
End If
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes

 

[The Code Cage Team]

I want to fixed the cell to enter the data in specific range 5 to 100
or
cell
within the specefic range that i will provide .You want to lock all other cells except the range you specify?

--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)

 

[Simon Lloyd]

I want to fixed the cell to enter the data in specific range 5 to 100 or
cell
within the specefic range that i will provide .Locking cells will not do anything unless you protect the worksheet.

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Imran]

Thanks jacob, yes it is working through data validation but i want to control
it through vb programming as you mentioned below option2. but option vb
program is not working. i want to set the value with in a limit in a specific
cell eg. (a1 or b1) through vb same through minimum value to maximum value.
waiting your reply.

 

[Imran]

Thanks dear but i want to fixed the cell eg (a1 or b1) through vb program.
can you wrote it in vb. thanks for cooperation.


imran

 

[Imran]

Dear Jacob thanks dear vb programm is also working now but if i want to same
for another cell then how i can fix same with "a1" cell and "b1" & c1". can
you help me to fixed the 3 or 4 cell with in a program.

thanks in advance
imran

 

[Jacob Skaria]

Imran, change the range in App,Intersect as below...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCol As Integer
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:C1")) Is Nothing Then
If Target.Value < 5 Or Target.Value > 100 Then
MsgBox " Invalid Entry": Target.Value = ""
End If
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes

 

[Imran]

Thank you very much jacob but i want to fixed the cells with differend value
ranges through this we can fixed a same value range for given range cells.

thanks

Imran

 

[Imran]

Dear Jacob,

for A1 value range is 5-100
for B1 value range is 101-200
for C1 value range is 201-300

really i am very thank full to you for your kind support

 

[Jacob Skaria]

Imran. try the below and feedback

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCol As Integer
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
If Target.Value < 5 Or Target.Value > 100 Then
MsgBox " Invalid Entry": Target.Value = ""
End If
ElseIf Not Application.Intersect(Target, Range("B1")) Is Nothing Then
If Target.Value < 101 Or Target.Value > 200 Then
MsgBox " Invalid Entry": Target.Value = ""
End If
ElseIf Not Application.Intersect(Target, Range("C1")) Is Nothing Then
If Target.Value < 201 Or Target.Value > 300 Then
MsgBox " Invalid Entry": Target.Value = ""
End If
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes

 

[Imran]

Thanks jacob you done a great work. really you r genious. can i have your
direct email adress if i face any problem so i can contact you drectly your
eamil adress

again thanks