Hidden worksheet codes not working

[Kaniguan1969]

Hi,

I have a workbook with 4 worksheets and contains a userform. running the workbook i put a codes to hide the 3 sheets contains a data.
When performing a data entry the codes that validate the a duplicate records is not working. I check the active worksheet which should be the transactionDB but upon checking the main sheets is the activate. When the worksheet is unhide this codes is working. Any advice. thanks.

here is my goods.




Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim TextBox1_found_blank As Boolean
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets("TransactionDB")
--upon checking the active worksheet is not transactionDB
If Me.TextBox1.Value = "" Then
TextBox1_found_blank = True
ElseIf Application.CountIf([A1:A10], Me.TextBox1.Value) > 1 Then
MsgBox "Duplicate Ticket Number Found."
Cancel = True
Exit Sub
Else
TextBox1_found_blank = False
End If
End Sub

[joms]

how do you call the sub proc from the active worksheet?

it should work..the worksheet is just hidden.

[Rob342]

Try


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim TextBox1_found_blank As Boolean
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets("TransactionDB")
'--upon checking the active worksheet Is Not transactionDB
with ws
If Me.TextBox1.Value = "" Then
TextBox1_found_blank = True
ElseIf Application.CountIf([A1:A10], Me.TextBox1.Value) > 1 Then
MsgBox "Duplicate Ticket Number Found."
Cancel = True
Exit Sub
Else
TextBox1_found_blank = False
End If
End with
End Sub


Rob

[Kaniguan1969]

Hi Joms and ROb,

Just made adjustment in the codes but still the trasactionDB sheet did not access intead the "Main" sheet were performed.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim TextBox1_found_blank As Boolean
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets("TransactionDB")
my_sheet = ActiveSheet.Name ----the value is "Main"
With ws
If Me.TextBox1.Value = "" Then
TextBox1_found_blank = True
ElseIf Application.CountIf([A1:A10], Me.TextBox1.Value) > 1 Then
MsgBox "Duplicate Ticket Number Found."
Cancel = True
Exit Sub
Else
TextBox1_found_blank = False
End If
End With

End Sub

[SamT]

The only error I see in the code is the location of "Cancel = True."

However, I added a line to activate "TransactionsDB" Try this code with and without that line.


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim TextBox1_found_blank As Boolean
Dim wb [As Workbook
Dim ws As Worksheet

Cancel = True 'Move here

Set wb = ThisWorkbook
Set ws = wb.Sheets("TransactionDB")

ws.Activate 'Try without this

With ws
If Me.TextBox1.Value = "" Then
TextBox1_found_blank = True
ElseIf Application.Count If ([A1:A10], Me.TextBox1.Value) > 1 Then
MsgBox "Duplicate Ticket Number Found."
Exit Sub
Else
TextBox1_found_blank = False
End If
End With

End Sub

[Kaniguan1969]

Still not working. so instead of hiding the worksheet, I try to unhide temporary.
still getting the "main" sheets instead of "transactionDB"

[SamT]

You have some code somewhere that is activating "Main."

I would look at any code that is on the sheet "Main."

Can you upload the workbook for us to examine? Use the "Go Advanced" button at the bottom of this post editor, then look under the Advanced editor for the "Manage Attachments" button.

If you have many rows of data, delete all rows except the top 10 on each sheet. Be sure to obfuscate any personal or proprietary information.