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.