Hello All,<o
></o>
I have Sheet1 , Sheet2 and Sheet3 in a workbook. <o></o>
Can anyone edit the code vba code below to make it work justonly SHEET1 and not other sheets. <o></o>
Thank you.
PubliccurSheet<o></o>
PrivateSub btnsearch1_Click()<o></o>
Dimi, LastRow, caseFound As Boolean<o></o>
Application.ScreenUpdating= False<o></o>
cassFound= False<o></o>
IfMe.txtSrch = "" Then<o></o>
ExitSub<o></o>
EndIf<o></o>
curSheet= ActiveSheet.Name<o></o>
'hideLocation 2 and 3 fields if next case does not have multiple locations<o></o>
Me.txtLoc2.Visible= False<o></o>
Me.txtLoc2.Value= ""<o></o>
Me.lblLoc2.Visible= False<o></o>
Me.txtLoc3.Visible= False<o></o>
Me.txtLoc3.Value= ""<o></o>
Me.lblLoc3.Visible= False<o></o>
ForEach ws In Sheets<o></o>
ws.Activate<o></o>
<o></o>
'set LastRow for current worksheet beingsearched<o></o>
LastRow= ws.Range("A" & Rows.count).End(xlUp).Row 'set last row number for current worksheet<o></o>
'loop through column C of worksheet beingsearched and evaluated case number match<o></o>
Fori = 2 To LastRow<o></o>
IfUCase(Cells(i, "A").Value) = UCase(Me.txtSrch) Then 'convert to upper case for matching. VBA is case sensitive<o></o>
caseFound= True<o></o>
'Me.txtDname= ws.Range("D" & i).Value<o></o>
IfMe.txtLoc.Value = "" Then<o></o>
Me.txtLoc= ws.Range("B" & i).Value<o></o>
EndIf<o></o>
'if a second location exists for casenumber, show Loc2 label and text box and enter the location<o></o>
IfMe.txtLoc2.Value = "" And ws.Range("B" & i).Value<> Me.txtLoc Then<o></o>
Me.txtLoc2.Visible= True<o></o>
Me.lblLoc2.Visible= True<o></o>
Me.txtLoc2.Value= ws.Range("B" & i).Value<o></o>
EndIf<o></o>
'if a third location exists for case number,show Loc3 label and text box and enter the location<o></o>
IfMe.txtLoc3.Value = "" And Me.txtLoc2.Value <> "" Andws.Range("B" & i).Value <> Me.txtLoc2 Andws.Range("B" & i).Value <> Me.txtLoc Then<o></o>
Me.txtLoc3.Visible= True<o></o>
Me.lblLoc3.Visible= True<o></o>
Me.txtLoc3.Value= ws.Range("B" & i).Value<o></o>
EndIf<o></o>
EndIf<o></o>
Nexti<o></o>
Nextws<o></o>
Sheets(curSheet).Activate<o></o>
'Me.txtDname.SetFocus<o></o>
IfcaseFound = False Then<o></o>
MsgBox"No record found for Case " & Me.txtSrch.Value, vbInformation,"Not Found"<o></o>
EndIf<o></o>
EndSub<o></o>
<o> </o>
PrivateSub txtSrch_Enter()<o></o>
Me.txtSrch.Value= ""<o></o>
Me.txtSrch.BackColor= &HFFFF& 'added for colortabuserform<o></o>
'Me.txtDname.Value= ""<o></o>
Me.txtLoc.Value= ""<o></o>
Me.txtLoc2.Value= ""<o></o>
Me.txtLoc3.Value= ""<o></o>
EndSub<o></o>
<o> </o>
PrivateSub UserForm_Activate()<o></o>
Me.lblLoc2.Visible= False<o></o>
Me.txtLoc2.Visible= False<o></o>
Me.lblLoc3.Visible= False<o></o>
Me.txtLoc3.Visible
></o>I have Sheet1 , Sheet2 and Sheet3 in a workbook. <o
></o>Can anyone edit the code vba code below to make it work justonly SHEET1 and not other sheets. <o
></o>Thank you.
PubliccurSheet<o
></o>PrivateSub btnsearch1_Click()<o
></o>Dimi, LastRow, caseFound As Boolean<o
></o>Application.ScreenUpdating= False<o
></o>cassFound= False<o
></o>IfMe.txtSrch = "" Then<o
></o>ExitSub<o
></o>EndIf<o
></o>curSheet= ActiveSheet.Name<o
></o>'hideLocation 2 and 3 fields if next case does not have multiple locations<o
></o>Me.txtLoc2.Visible= False<o
></o>Me.txtLoc2.Value= ""<o
></o>Me.lblLoc2.Visible= False<o
></o>Me.txtLoc3.Visible= False<o
></o>Me.txtLoc3.Value= ""<o
></o>Me.lblLoc3.Visible= False<o
></o>ForEach ws In Sheets<o
></o>ws.Activate<o
></o><o
></o>'set LastRow for current worksheet beingsearched<o
></o>LastRow= ws.Range("A" & Rows.count).End(xlUp).Row 'set last row number for current worksheet<o
></o>'loop through column C of worksheet beingsearched and evaluated case number match<o
></o>Fori = 2 To LastRow<o
></o>IfUCase(Cells(i, "A").Value) = UCase(Me.txtSrch) Then 'convert to upper case for matching. VBA is case sensitive<o
></o>caseFound= True<o
></o>'Me.txtDname= ws.Range("D" & i).Value<o
></o>IfMe.txtLoc.Value = "" Then<o
></o>Me.txtLoc= ws.Range("B" & i).Value<o
></o>EndIf<o
></o>'if a second location exists for casenumber, show Loc2 label and text box and enter the location<o
></o>IfMe.txtLoc2.Value = "" And ws.Range("B" & i).Value<> Me.txtLoc Then<o
></o>Me.txtLoc2.Visible= True<o
></o>Me.lblLoc2.Visible= True<o
></o>Me.txtLoc2.Value= ws.Range("B" & i).Value<o
></o>EndIf<o
></o>'if a third location exists for case number,show Loc3 label and text box and enter the location<o
></o>IfMe.txtLoc3.Value = "" And Me.txtLoc2.Value <> "" Andws.Range("B" & i).Value <> Me.txtLoc2 Andws.Range("B" & i).Value <> Me.txtLoc Then<o
></o>Me.txtLoc3.Visible= True<o
></o>Me.lblLoc3.Visible= True<o
></o>Me.txtLoc3.Value= ws.Range("B" & i).Value<o
></o>EndIf<o
></o>EndIf<o
></o>Nexti<o
></o>Nextws<o
></o>Sheets(curSheet).Activate<o
></o>'Me.txtDname.SetFocus<o
></o>IfcaseFound = False Then<o
></o>MsgBox"No record found for Case " & Me.txtSrch.Value, vbInformation,"Not Found"<o
></o>EndIf<o
></o>EndSub<o
></o><o
> </o>PrivateSub txtSrch_Enter()<o
></o>Me.txtSrch.Value= ""<o
></o>Me.txtSrch.BackColor= &HFFFF& 'added for colortabuserform<o
></o>'Me.txtDname.Value= ""<o
></o>Me.txtLoc.Value= ""<o
></o>Me.txtLoc2.Value= ""<o
></o>Me.txtLoc3.Value= ""<o
></o>EndSub<o
></o><o
> </o>PrivateSub UserForm_Activate()<o
></o>Me.lblLoc2.Visible= False<o
></o>Me.txtLoc2.Visible= False<o
></o>Me.lblLoc3.Visible= False<o
></o>Me.txtLoc3.Visible
