Programming problem

  • Thread starter Thread starter Imran
  • Start date Start date
I

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
Click to expand...
within the specefic range that i will provide .
 
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
 
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.
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
Back
Top