MagicMike
11-13-2008, 03:31 PM
Hello all,
I am writing some code to determine if the text in two different fields on a form (work order number and revission number) are present on a consolodated data sheet. I basically want to reject the creation of a new work order number into the system if it already exsists. This may be convoluded but here goes.
1. I have the form code which calls the sub "Check_WO_Use()" when the submit button is pressed.
2. I should note that excel will already be on the correct sheet to search when this is run.
3. I have a public variable declared in a different module as:
'Work order number exsists
Public WO_Exsists As Boolean
so I can set the status to true or false and return to the form code for some If Thens there.
4. The sub "Entry_New()" is called to create a new entry.
module 1:
Sub Check_WO_Use()
'Is the proposed work order number already in use?
Dim Find_Range As Range
Dim Exsisting_WO As String
On Error Resume Next
Exsisting_WO = Format(Left(Home.TextBox_Exsisting_WO.Text, 3) & _
Right(Home.TextBox_Exsisting_WO.Text, 3)) & Format(Right(Home.ComboBox_WO_Rev.Text, 2))
'Check Next available WO to see if its being used
If Home.TextBox_Exsisting_WO.Value = "" Then
Set Find_Range = Cells.Find(What:=Format(Left(Home.TextBox_Next_Avail_WO.Text, 3) & _
Right(Home.TextBox_Next_Avail_WO.Text, 3)), After:=Range("A6"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
On Error GoTo 0
If Not Find_Range Is Nothing Then
MsgBox ("Available WO: This workorder number is already in use!")
WO_Exsists = True
Exit Sub
Else
MsgBox ("Available WO: This workorder number is available")
WO_Exsists = False
Call Entry_New
Exit Sub
End If
'Check Exsisting WO to see if its being used
Else
Set Find_Range = Cells.Find(What:=Exsisting_WO, After:=Range("A6"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
On Error GoTo 0
If Not Find_Range Is Nothing Then
MsgBox ("Exsisting WO: This workorder number is already in use!")
WO_Exsists = True
Exit Sub
Else
MsgBox ("Exsisting WO: This workorder number is available")
WO_Exsists = False
Call Entry_New
Exit Sub
End If
End If
End Sub 'Check_WO_Use()
In the form sub that called the "Check_WO_Use()" sub I have
Call Check_WO_Use
If WO_Exsists = True Then
Exit Sub
Else
End If
Below this is the command to do some emailing stuff which is not relavent to this.
My question is this:
This works ok when there is not an existing WO, however when I try to create a new work order that alreasdy exsists it reacts just like it does not exsist.
Does anyone see anything strange with my find if them logic? I'm hoping that something will stand out to someone on here.
Thank you,
-Mike
Edit: I wanted to add that when I do enter an exsisting wo it does find that cell on the sheet with the exsisting work order. I think there is something flawed in my if then statement and if not is nothing then part. or there is something wrong with how I am using the public WO_Exsists boolean.
I am writing some code to determine if the text in two different fields on a form (work order number and revission number) are present on a consolodated data sheet. I basically want to reject the creation of a new work order number into the system if it already exsists. This may be convoluded but here goes.
1. I have the form code which calls the sub "Check_WO_Use()" when the submit button is pressed.
2. I should note that excel will already be on the correct sheet to search when this is run.
3. I have a public variable declared in a different module as:
'Work order number exsists
Public WO_Exsists As Boolean
so I can set the status to true or false and return to the form code for some If Thens there.
4. The sub "Entry_New()" is called to create a new entry.
module 1:
Sub Check_WO_Use()
'Is the proposed work order number already in use?
Dim Find_Range As Range
Dim Exsisting_WO As String
On Error Resume Next
Exsisting_WO = Format(Left(Home.TextBox_Exsisting_WO.Text, 3) & _
Right(Home.TextBox_Exsisting_WO.Text, 3)) & Format(Right(Home.ComboBox_WO_Rev.Text, 2))
'Check Next available WO to see if its being used
If Home.TextBox_Exsisting_WO.Value = "" Then
Set Find_Range = Cells.Find(What:=Format(Left(Home.TextBox_Next_Avail_WO.Text, 3) & _
Right(Home.TextBox_Next_Avail_WO.Text, 3)), After:=Range("A6"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
On Error GoTo 0
If Not Find_Range Is Nothing Then
MsgBox ("Available WO: This workorder number is already in use!")
WO_Exsists = True
Exit Sub
Else
MsgBox ("Available WO: This workorder number is available")
WO_Exsists = False
Call Entry_New
Exit Sub
End If
'Check Exsisting WO to see if its being used
Else
Set Find_Range = Cells.Find(What:=Exsisting_WO, After:=Range("A6"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
On Error GoTo 0
If Not Find_Range Is Nothing Then
MsgBox ("Exsisting WO: This workorder number is already in use!")
WO_Exsists = True
Exit Sub
Else
MsgBox ("Exsisting WO: This workorder number is available")
WO_Exsists = False
Call Entry_New
Exit Sub
End If
End If
End Sub 'Check_WO_Use()
In the form sub that called the "Check_WO_Use()" sub I have
Call Check_WO_Use
If WO_Exsists = True Then
Exit Sub
Else
End If
Below this is the command to do some emailing stuff which is not relavent to this.
My question is this:
This works ok when there is not an existing WO, however when I try to create a new work order that alreasdy exsists it reacts just like it does not exsist.
Does anyone see anything strange with my find if them logic? I'm hoping that something will stand out to someone on here.
Thank you,
-Mike
Edit: I wanted to add that when I do enter an exsisting wo it does find that cell on the sheet with the exsisting work order. I think there is something flawed in my if then statement and if not is nothing then part. or there is something wrong with how I am using the public WO_Exsists boolean.