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 .

Use Data Validation.
--
Gary''s Student - gsnu200856


"Imran" wrote:

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 .

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


"Gary''s Student" wrote:

Use Data Validation.
--
Gary''s Student - gsnu200856


"Imran" wrote:

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 .

Hi Imran

1. Use Data validtionAllow 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
---------------
Jacob Skaria


"Imran" wrote:

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 .

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.

"Jacob Skaria" wrote:

Hi Imran

1. Use Data validtionAllow 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
---------------
Jacob Skaria


"Imran" wrote:

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 .

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" wrote:

Hi Imran

1. Use Data validtionAllow 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
---------------
Jacob Skaria


"Imran" wrote:

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 .

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
---------------
Jacob Skaria


"Imran" wrote:

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" wrote:

Hi Imran

1. Use Data validtionAllow 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
---------------
Jacob Skaria


"Imran" wrote:

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 .

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" wrote:

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
---------------
Jacob Skaria


"Imran" wrote:

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" wrote:

Hi Imran

1. Use Data validtionAllow 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
---------------
Jacob Skaria


"Imran" wrote:

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 .

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
---------------
Jacob Skaria


"Imran" wrote:

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" wrote:

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
---------------
Jacob Skaria


"Imran" wrote:

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" wrote:

Hi Imran

1. Use Data validtionAllow 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
---------------
Jacob Skaria


"Imran" wrote:

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 .

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
---------------
Jacob Skaria


"Imran" wrote:

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" wrote:

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
---------------
Jacob Skaria


"Imran" wrote:

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" wrote:

Hi Imran

1. Use Data validtionAllow 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
---------------
Jacob Skaria


"Imran" wrote:

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 .

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Imran" wrote:

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" wrote:

Hi Imran

1. Use Data validtionAllow 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
---------------
Jacob Skaria


"Imran" wrote:

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 .

Imran;371551 Wrote:
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 .Locking cells will not do anything unless you protect the worksheet.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=104040

Imran;371551 Wrote:
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 .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)
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=104040