Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable/Input box problem
Hi i have a program that takes information from inputboxes. The user
enters the info in and it adds it to a list within the spreadsheet. The info entered can be text or numerical. When the user enters the info the program looks to see if they have already entered that info onto the list. If the user adds text it checks the list and returns a message if they have already entered that text. But when i enter a number that i have already entered then it still allow me to input it onto the list. I have tried declaring the variable assigned to the input box as an integer but get an integer, it then checks the numbers entered ok, but throws out an error if i eneter a text value. here the bit of code i am using. any help greatly recieved. Sub ADDMAN() Dim clear Dim JOBCHECK 'this bit makes sure the users enters something' Do While clear = "" clear = InputBox("Please enter A1024/D-Pole Ref") Loop 'this bit checks the info has not already been entered' JOBCHECK = 0 Sheets("WORKLIST").Select Range("A1").Select Do While Selection.Value < "" ActiveCell.Offset(1, 0).Select If ActiveCell = clear Then JOBCHECK = 1 Loop If JOBCHECK = 1 Then MsgBox Title:="POLETRACKER", PROMPT:="SORRY THIS JOB ALREADY EXISTS ON YOUR CURRENT WORKSTACK!" If JOBCHECK = 1 Then Sheets("MENU").Select If JOBCHECK = 1 Then exit sub 'THIS BIT ENTERS THE VALUE TO THE LIST' Sheets("WORKLIST").Select Range("A1").Select Do While Selection.Value < "" ActiveCell.Offset(1, 0).Select Loop ActiveCell = clear END SUB |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable/Input box problem
If lcase(Trim(ActiveCell.Text)) = lcase(Trim(cStr(clear))) Then JOBCHECK = 1
-- Regards. Tom Ogilvy wrote in message ups.com... Hi i have a program that takes information from inputboxes. The user enters the info in and it adds it to a list within the spreadsheet. The info entered can be text or numerical. When the user enters the info the program looks to see if they have already entered that info onto the list. If the user adds text it checks the list and returns a message if they have already entered that text. But when i enter a number that i have already entered then it still allow me to input it onto the list. I have tried declaring the variable assigned to the input box as an integer but get an integer, it then checks the numbers entered ok, but throws out an error if i eneter a text value. here the bit of code i am using. any help greatly recieved. Sub ADDMAN() Dim clear Dim JOBCHECK 'this bit makes sure the users enters something' Do While clear = "" clear = InputBox("Please enter A1024/D-Pole Ref") Loop 'this bit checks the info has not already been entered' JOBCHECK = 0 Sheets("WORKLIST").Select Range("A1").Select Do While Selection.Value < "" ActiveCell.Offset(1, 0).Select If ActiveCell = clear Then JOBCHECK = 1 Loop If JOBCHECK = 1 Then MsgBox Title:="POLETRACKER", PROMPT:="SORRY THIS JOB ALREADY EXISTS ON YOUR CURRENT WORKSTACK!" If JOBCHECK = 1 Then Sheets("MENU").Select If JOBCHECK = 1 Then exit sub 'THIS BIT ENTERS THE VALUE TO THE LIST' Sheets("WORKLIST").Select Range("A1").Select Do While Selection.Value < "" ActiveCell.Offset(1, 0).Select Loop ActiveCell = clear END SUB |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable/Input box problem
John,
Change If ActiveCell = clear Then JOBCHECK = 1 to If ActiveCell = CStr(clear) Then JOBCHECK = 1 But, there isn't any need for all that looping. See the example below, which also has better entry checking and loops back to the beginning if the entry was already found. HTH, Bernie MS Excel MVP Sub AddMan() Dim strClear As String GetEntry: strClear = "" 'this bit makes sure the users enters something' Do While strClear = "" strClear = Application.InputBox("Please enter A1024/D-Pole Ref") If strClear = "False" Then Exit Sub ' User hit cancel Loop 'this bit checks the info has not already been entered' If Application.CountIf(Sheets("WORKLIST").Range("A:A" ), _ strClear) = 0 Then Range("A65536").End(xlUp)(2).Value = strClear Else MsgBox "Sorry, job already in the workstack.", _ vbOKOnly, "PoleTracker" Sheets("MENU").Select GoTo GetEntry End If End Sub wrote in message ups.com... Hi i have a program that takes information from inputboxes. The user enters the info in and it adds it to a list within the spreadsheet. The info entered can be text or numerical. When the user enters the info the program looks to see if they have already entered that info onto the list. If the user adds text it checks the list and returns a message if they have already entered that text. But when i enter a number that i have already entered then it still allow me to input it onto the list. I have tried declaring the variable assigned to the input box as an integer but get an integer, it then checks the numbers entered ok, but throws out an error if i eneter a text value. here the bit of code i am using. any help greatly recieved. Sub ADDMAN() Dim clear Dim JOBCHECK 'this bit makes sure the users enters something' Do While clear = "" clear = InputBox("Please enter A1024/D-Pole Ref") Loop 'this bit checks the info has not already been entered' JOBCHECK = 0 Sheets("WORKLIST").Select Range("A1").Select Do While Selection.Value < "" ActiveCell.Offset(1, 0).Select If ActiveCell = clear Then JOBCHECK = 1 Loop If JOBCHECK = 1 Then MsgBox Title:="POLETRACKER", PROMPT:="SORRY THIS JOB ALREADY EXISTS ON YOUR CURRENT WORKSTACK!" If JOBCHECK = 1 Then Sheets("MENU").Select If JOBCHECK = 1 Then exit sub 'THIS BIT ENTERS THE VALUE TO THE LIST' Sheets("WORKLIST").Select Range("A1").Select Do While Selection.Value < "" ActiveCell.Offset(1, 0).Select Loop ActiveCell = clear END SUB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable sum from defined input | Excel Worksheet Functions | |||
With QUERY how to input a variable | Excel Discussion (Misc queries) | |||
Formulas containing variable input | Excel Discussion (Misc queries) | |||
Input box with variable | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |