Add information to hidden sheets without revealing hidden sheets

[deadlyjack]

Hi,
I've created a login function for my excel workbook. Where a user that is not an admin logs in, multiple sheets are hidden...
From the visible sheets, the user can add important information, through a Userform, that information once applied will be collected in a hidden spreadsheet.
How do I code acceptance of adding into hidden sheets without revealing them accurately?

Hidden sheets VBA:

Global nextID As Integer

________________________________________________________________

Public Sub GenerateUniqueID()
       Dim LargestID() As Integer
       Dim i As Integer

       ReDim largestID(1 To Worksheets.Count - 1)

      For i = 7 To Worksheets.Count
            Worksheets(i).Select
            Range("A2").Select
            Range(Selection, Selection.End(xlDown)).Select

            largestID(i -1) = Application.Max(Selection)
      Next i

      NextID = Application.Max(largestID) + 1

I've got brainfreeze right now... So how do I make my Userform accept input to these hidden sheets?

 

[bobsan42]

Don't use SELECT.
Refer to the sheets and ranges directly

 

[GWteB]

Don't use statements like Select or Activate but make proper references so you're able to access the objects directly:

Sub deadlyjack()

    Dim LargestID() As Integer
    Dim i As Integer

    ReDim LargestID(1 To Worksheets.Count - 1)

    Dim myRange As Range              ' <<<<<<<<
    For i = 7 To Worksheets.Count
        With Worksheets(i)
            Set myRange = .Range("A2:A" & .Cells(2, "A").End(xlDown).Row)
        End With
        LargestID(i - 1) = Application.Max(myRange)
    Next i

    nextID = Application.Max(LargestID) + 1
End Sub

 

[deadlyjack]

Am getting error 1004, while changing that code... ?
Works fine while logged in with username as Admin, but when I log back into the non-admin, where the majority of sheets are hidden, the error pops up and no information has been sent to the hidden sheets.

Isn't there a
"if sheet = hidden"
sort of code I need to add in order for Excel to accept dumps to hidden sheets?

 

[bobsan42]

Which line triggers the error?

 

[deadlyjack]

It's from my CommandButton Submit the code is coming from

Private Sub cmdSubmit_click()
      If Me.txtEndTime.Value = "" Then
                MsgBox ("Please verify by clicking, STOP PRODUCTION, before proceeding to the next phase.")
      Else
             GenerateUniqueID

             Worksheets(Me.cboSort.Value).Select    '<--------------- This one gets Error 1004

             Range("A1).Select

             If ActiveCell.Offset(1, 0).Value = "" Then
                   ActiveCell.Offset(1, 0).Select
             Else
                   Selection.End(xlDown).Offset(1, 0).Select
             End If

             ActiveCell.Value = nextID
             ActiveCell.Offset(0, 1).Value = Me.cboSignatur.Value
             ActiveCell.Offset(0, 1).Value = Me.cboSort.Value
             '... etc etc...

End Sub

 

[bobsan42]

First, I believe SELECT is not a method available to worksheets.
Second, i think we already advised on not using select and activate on hidden sheets.

 

[deadlyjack]

Ah, so I gotto restructure the whole thing if I want them to be applied to hidden sheets ?
I'll get on it once I got more time.

Thanks for the heads up!

 

[deadlyjack]

I noticed that I get a problem with your code when it comes to the uniqueID(!!!). When trying to access the objects directly, my uniqueID constantly comes back as 1... I need this number to always increase by 1, otherwise it's pointless.

I've changed back to my original code, that works... All I need to figure out is how to create code to dump information to the hidden sheets ?

Else just lock them completely, yet visible...

 

[GWteB]

I noticed that I get a problem with your code when it comes to the uniqueID(!!!).

If you're addressing me, the code I supplied in my post #3 is almost an exact copy of your own code.
I have only changed the three lines below, because one cannot select worksheet ranges if a worksheet is hidden, but if you make a proper reference (like I did) you certainly have read/write access to a hidden worksheet, and that was mainly your issue if I recall.

Worksheets(i).Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select

' became:
With Worksheets(i)
    Set myRange = .Range("A2:A" & .Cells(2, "A").End(xlDown).Row)
End With