code help

J

John

Hi can someone help me please

I am using the following code to create a series of input boxes to input
some data into a row
I need to inhibit the first column so that it stops anyone entering 02 or 06

' Telephone Data Entry
' To enter all new call records using Input Boxes
'
' Keyboard Shortcut: Ctrl+T'

TelephoneNumber = InputBox("Enter the first 2 digits of telephone
Number")
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
' xlEqual, Formula1:="02",Formula2:="06"'
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Enter first 2 digits only"
.ErrorTitle = "Mobile or Premium Line Number"
.InputMessage = "Please enter only the first 2 digits of the
telephone number"
.ErrorMessage = "This is a mobile or premium line number - enter the
complete telephone number in the next column and leave this column blank"
.ShowInput = True
.ShowError = True
End With

ActiveCell.FormulaR1C1 = TelephoneNumber
ActiveCell.Offset(0, 1).Range("A1").Select
RestofTelephoneNumber = InputBox("Enter the rest of the telephone
number")
ActiveCell.FormulaR1C1 = RestofTelephoneNumber
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(0, 1).Range("A1").Select
Duration = InputBox("Enter duration of call")
ActiveCell.FormulaR1C1 = Duration
ActiveCell.Offset(0, 1).Range("A1").Select
Description = InputBox("Enter area where call was made to")
ActiveCell.FormulaR1C1 = Description
ActiveCell.Offset(0, 1).Range("A1").Select
TimeBand = InputBox("Enter Peak or Off-Peak Call")
ActiveCell.FormulaR1C1 = TimeBand
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=""Plymouth - Local"",""Local"",""Long Distance"")"
ActiveCell.Offset(0, 1).Range("A1").Select
CallCost = InputBox("Enter the cost of the call")
ActiveCell.FormulaR1C1 = CallCost
ActiveCell.Select
Selection.NumberFormat = "0.00"
ActiveCell.Offset(1, -8).Range("A1").Select
End Sub


John
 
D

Dave Peterson

You want to stop the user from typing 02 or 06 in the first inputbox?


do
TelephoneNumber = InputBox("Enter the first 2 digits of telephone Number")
if telephonenumber = "02" _
or telephonenumber = "06" then
msgbox "Please try again"
else
exit do
end if
loop

Hi can someone help me please

I am using the following code to create a series of input boxes to input
some data into a row
I need to inhibit the first column so that it stops anyone entering 02 or 06

' Telephone Data Entry
' To enter all new call records using Input Boxes
'
' Keyboard Shortcut: Ctrl+T'

TelephoneNumber = InputBox("Enter the first 2 digits of telephone
Number")
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
' xlEqual, Formula1:="02",Formula2:="06"'
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Enter first 2 digits only"
.ErrorTitle = "Mobile or Premium Line Number"
.InputMessage = "Please enter only the first 2 digits of the
telephone number"
.ErrorMessage = "This is a mobile or premium line number - enter the
complete telephone number in the next column and leave this column blank"
.ShowInput = True
.ShowError = True
End With

ActiveCell.FormulaR1C1 = TelephoneNumber
ActiveCell.Offset(0, 1).Range("A1").Select
RestofTelephoneNumber = InputBox("Enter the rest of the telephone
number")
ActiveCell.FormulaR1C1 = RestofTelephoneNumber
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(0, 1).Range("A1").Select
Duration = InputBox("Enter duration of call")
ActiveCell.FormulaR1C1 = Duration
ActiveCell.Offset(0, 1).Range("A1").Select
Description = InputBox("Enter area where call was made to")
ActiveCell.FormulaR1C1 = Description
ActiveCell.Offset(0, 1).Range("A1").Select
TimeBand = InputBox("Enter Peak or Off-Peak Call")
ActiveCell.FormulaR1C1 = TimeBand
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=""Plymouth - Local"",""Local"",""Long Distance"")"
ActiveCell.Offset(0, 1).Range("A1").Select
CallCost = InputBox("Enter the cost of the call")
ActiveCell.FormulaR1C1 = CallCost
ActiveCell.Select
Selection.NumberFormat = "0.00"
ActiveCell.Offset(1, -8).Range("A1").Select
End Sub

John
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

works in 2007 but not in 2010 0
Auto Send Email from Excel 2007 No Error when Outlook 2007 is full 0
Filename Problem In a Macro 4
adding a semicolon 4
loop until empty row and column problem (format cells until the lastrow and column in a report) 4
Pausing VBA execution to allow for full recalculation 1
how to use Solver in Excel VBA 1
Looping through records 2

Top