Help with IF statement
 

Hello everyone

I have a working IF statement which I have been atempting to modify but I
cannot get it to do what I want, could someone please help.

The value of a cell is based on the value entered into a textbox
I can enter the names into the cell by entering code numbers into the
textbox

If anything other that the code numbers are entered I would like a message
box to pop up saying invalid entry. When OK is clicked the application
should close.

Here is the part of the code I am trying to alter (it is run from a button
on a userform which contains the textbox)

With LogEntry

If ActiveCell.Offset(0, 12).Value = "" Then

With LogEntry.TextBox6

If .Value = "001" Then

ActiveCell.Offset(0, 12).Value = "Kenny Wharton"

End If

If .Value = "002" Then

ActiveCell.Offset(0, 12).Value = "Karl Eason"

End If

If .Value = "003" Then

ActiveCell.Offset(0, 12).Value = "Len Stobbs"

End If

End With



any help would be appreciated

Kenny

Various combinations of Windows ME, 98 and 2000

and Office 97 and 2000

Help with IF statement
 

Hi Forum Freak

Maybe this will give you some ideas

Sub test()
If Not IsNumeric(TextBox1) Then
MsgBox "invalid entry"
Else
If TextBox1.Value = 1 Then Range("A1").Value = "Judith"
If TextBox1.Value = 2 Then Range("A1").Value = "jelle"
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Forum Freak" wrote in message ...
Hello everyone

I have a working IF statement which I have been atempting to modify but I
cannot get it to do what I want, could someone please help.

The value of a cell is based on the value entered into a textbox
I can enter the names into the cell by entering code numbers into the
textbox

If anything other that the code numbers are entered I would like a message
box to pop up saying invalid entry. When OK is clicked the application
should close.

Here is the part of the code I am trying to alter (it is run from a button
on a userform which contains the textbox)

With LogEntry

If ActiveCell.Offset(0, 12).Value = "" Then

With LogEntry.TextBox6

If .Value = "001" Then

ActiveCell.Offset(0, 12).Value = "Kenny Wharton"

End If

If .Value = "002" Then

ActiveCell.Offset(0, 12).Value = "Karl Eason"

End If

If .Value = "003" Then

ActiveCell.Offset(0, 12).Value = "Len Stobbs"

End If

End With



any help would be appreciated

Kenny

Various combinations of Windows ME, 98 and 2000

and Office 97 and 2000

Help with IF statement
 

Thanks Ron, but that does not help as I need to reject some numerics!
All I want to allow is "001" "002" and "003" - absolutely nothing else.
Is it possible?

Kenny

"Ron de Bruin" wrote in message
...
Hi Forum Freak

Maybe this will give you some ideas

Sub test()
If Not IsNumeric(TextBox1) Then
MsgBox "invalid entry"
Else
If TextBox1.Value = 1 Then Range("A1").Value = "Judith"
If TextBox1.Value = 2 Then Range("A1").Value = "jelle"
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Forum Freak" wrote in message
...
Hello everyone

I have a working IF statement which I have been atempting to modify but
I
cannot get it to do what I want, could someone please help.

The value of a cell is based on the value entered into a textbox
I can enter the names into the cell by entering code numbers into the
textbox

If anything other that the code numbers are entered I would like a
message
box to pop up saying invalid entry. When OK is clicked the application
should close.

Here is the part of the code I am trying to alter (it is run from a
button
on a userform which contains the textbox)

With LogEntry

If ActiveCell.Offset(0, 12).Value = "" Then

With LogEntry.TextBox6

If .Value = "001" Then

ActiveCell.Offset(0, 12).Value = "Kenny Wharton"

End If

If .Value = "002" Then

ActiveCell.Offset(0, 12).Value = "Karl Eason"

End If

If .Value = "003" Then

ActiveCell.Offset(0, 12).Value = "Len Stobbs"

End If

End With



any help would be appreciated

Kenny

Various combinations of Windows ME, 98 and 2000

and Office 97 and 2000

Help with IF statement
 

Why do you use a textbox?
Use DataValidation..and the list option

Enter 001,002,003


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Forum Freak" wrote in message ...
Thanks Ron, but that does not help as I need to reject some numerics!
All I want to allow is "001" "002" and "003" - absolutely nothing else.
Is it possible?

Kenny

"Ron de Bruin" wrote in message
...
Hi Forum Freak

Maybe this will give you some ideas

Sub test()
If Not IsNumeric(TextBox1) Then
MsgBox "invalid entry"
Else
If TextBox1.Value = 1 Then Range("A1").Value = "Judith"
If TextBox1.Value = 2 Then Range("A1").Value = "jelle"
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Forum Freak" wrote in message
...
Hello everyone

I have a working IF statement which I have been atempting to modify but
I
cannot get it to do what I want, could someone please help.

The value of a cell is based on the value entered into a textbox
I can enter the names into the cell by entering code numbers into the
textbox

If anything other that the code numbers are entered I would like a
message
box to pop up saying invalid entry. When OK is clicked the application
should close.

Here is the part of the code I am trying to alter (it is run from a
button
on a userform which contains the textbox)

With LogEntry

If ActiveCell.Offset(0, 12).Value = "" Then

With LogEntry.TextBox6

If .Value = "001" Then

ActiveCell.Offset(0, 12).Value = "Kenny Wharton"

End If

If .Value = "002" Then

ActiveCell.Offset(0, 12).Value = "Karl Eason"

End If

If .Value = "003" Then

ActiveCell.Offset(0, 12).Value = "Len Stobbs"

End If

End With



any help would be appreciated

Kenny

Various combinations of Windows ME, 98 and 2000

and Office 97 and 2000

Help with IF statement
 

Sub test()
Select Case TextBox1.Text
Case "001": ActiveCell.Offset(0, 12).Value = "Kenny Wharton"
Case "002": ActiveCell.Offset(0, 12).Value = "Karl Eason"
Case "003": ActiveCell.Offset(0, 12).Value = "Len Stobbs"
Case Else:
MsgBox "Invalid Value"
With Textbox1
.SelStart = 1
.Sellength = Len(.Text)
.SetFocus)
End With
End Select
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Forum Freak" wrote in message
...
Thanks Ron, but that does not help as I need to reject some numerics!
All I want to allow is "001" "002" and "003" - absolutely nothing else.
Is it possible?

Kenny

"Ron de Bruin" wrote in message
...
Hi Forum Freak

Maybe this will give you some ideas

Sub test()
If Not IsNumeric(TextBox1) Then
MsgBox "invalid entry"
Else
If TextBox1.Value = 1 Then Range("A1").Value = "Judith"
If TextBox1.Value = 2 Then Range("A1").Value = "jelle"
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Forum Freak" wrote in message
...
Hello everyone

I have a working IF statement which I have been atempting to modify
but
I
cannot get it to do what I want, could someone please help.

The value of a cell is based on the value entered into a textbox
I can enter the names into the cell by entering code numbers into the
textbox

If anything other that the code numbers are entered I would like a
message
box to pop up saying invalid entry. When OK is clicked the application
should close.

Here is the part of the code I am trying to alter (it is run from a
button
on a userform which contains the textbox)

With LogEntry

If ActiveCell.Offset(0, 12).Value = "" Then

With LogEntry.TextBox6

If .Value = "001" Then

ActiveCell.Offset(0, 12).Value = "Kenny Wharton"

End If

If .Value = "002" Then

ActiveCell.Offset(0, 12).Value = "Karl Eason"

End If

If .Value = "003" Then

ActiveCell.Offset(0, 12).Value = "Len Stobbs"

End If

End With



any help would be appreciated

Kenny

Various combinations of Windows ME, 98 and 2000

and Office 97 and 2000

Help with IF statement
 

Thanks Ron but I am working with a userform
Thanks Bob (yet again) that code did the trick!

Kenny
(Alias ForumFreak, Newbie1,KennyatWork, KennyatHome - depending which PC I
am sat at!!!)
"Ron de Bruin" wrote in message
...
Why do you use a textbox?
Use DataValidation..and the list option

Enter 001,002,003


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Forum Freak" wrote in message
...
Thanks Ron, but that does not help as I need to reject some numerics!
All I want to allow is "001" "002" and "003" - absolutely nothing else.
Is it possible?

Kenny

"Ron de Bruin" wrote in message
...
Hi Forum Freak

Maybe this will give you some ideas

Sub test()
If Not IsNumeric(TextBox1) Then
MsgBox "invalid entry"
Else
If TextBox1.Value = 1 Then Range("A1").Value = "Judith"
If TextBox1.Value = 2 Then Range("A1").Value = "jelle"
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Forum Freak" wrote in message
...
Hello everyone

I have a working IF statement which I have been atempting to modify
but
I
cannot get it to do what I want, could someone please help.

The value of a cell is based on the value entered into a textbox
I can enter the names into the cell by entering code numbers into
the
textbox

If anything other that the code numbers are entered I would like a
message
box to pop up saying invalid entry. When OK is clicked the
application
should close.

Here is the part of the code I am trying to alter (it is run from a
button
on a userform which contains the textbox)

With LogEntry

If ActiveCell.Offset(0, 12).Value = "" Then

With LogEntry.TextBox6

If .Value = "001" Then

ActiveCell.Offset(0, 12).Value = "Kenny Wharton"

End If

If .Value = "002" Then

ActiveCell.Offset(0, 12).Value = "Karl Eason"

End If

If .Value = "003" Then

ActiveCell.Offset(0, 12).Value = "Len Stobbs"

End If

End With



any help would be appreciated

Kenny

Various combinations of Windows ME, 98 and 2000

and Office 97 and 2000