Big Crash

[garyj]

Hello..

I don't know what happened. I was struggling with some code that kept failing, and eventually it crashed the program. At first - unable to restart it. Eventually managed to find a way, but only to grab changes and put them into an older copy. The recovery files had removed a couple ActiveX Command buttons, changing them into pictures. I manually caught up to where I was before-ish... did it a bit different and it was working even better now... and then the same thing. This time the file opens in Safe Mode for only a couple seconds and then crashes again. I can't keep it open long enough to make changes. The second time I had another copy, but still a couple hours of work lost.

Anyway, what I was doing both times was similar. Maybe someone will know an answer as I describe this....

My code...

Sub CheckUser()    
Dim UserRow, SheetCol, UserCol As Long
Dim SheetNm As String
Dim ColCt As Long
Dim CkAdmin As Integer
UserCol = Range("UserName").Column
Dim Mycell As Range
CkAdmin = 0
With Sheet17
    Set Mycell = Cells(.Range("AdmUsrCk").Value, UserCol)    '<----------------This was what crashed my first worksheet. 
    If .Range("B8").Value = Empty Then 'Incorrect Username 'AdmUsrCk
        MsgBox "Please enter a correct user name"
        Exit Sub
    End If
    If ChangePwd = True Then    '2nd Time thru, so new password saved in table
        Mycell.Offset(0, 1).Value = MyNewPwd                        '<----------------This was what crashed my second worksheet. 
        ChangePwd = False
        With frmLogin
             .PswdTxt.Value = ""
             .PswdLbl.Caption = "Password"
             .HiddenLbl.Visible = False
             .HiddenTxt.Visible = False
             .PswdTxt.Value = ""
             .HiddenTxt.Value = ""
         End With
    End If
    If .Range("B7").Value <> True Then 'Incorrect password  'AdmPwdCk
         MsgBox "Please enter a correct password"
         Exit Sub
    End If
    If NewPwdReq Then           'user has requested new password while logging in, so...
        With frmLogin                '1st time thru: check password, modify form for new pswd, wait for entry
              .PswdTxt.Value = ""
              .PswdLbl.Caption = "New Password"
              .HiddenLbl.Visible = True
              .HiddenTxt.Visible = True
              ChangePwd = True    'This sets variable for 2nd time thru.
          End With
          Exit Sub
    End If
    'Log-in is Complete - Now Set Sheets in either Editable, ReadOnly, or VeryHidden
    frmLogin.Hide
    UserRow = .Range("B8").Value 'User Row  AdmUsrCk
    '.Range("AdmUsrN,AdmPswd").ClearContents
    CkAdmin = Application.WorksheetFunction.CountIf(Range("SecurityAdmin"), "Ð")
    If CkAdmin = 0 Then .Range("AdminAdmin") = "Ð"
    ColCt = .Cells(4, Columns.Count).End(xlToLeft).Column
    Application.ScreenUpdating = False
    For SheetCol = Range("SecurityAdmin").Column To ColCt
         SheetNm = .Cells(4, SheetCol).Value 'Sheet Name
         If SheetNm = "" Then Exit Sub
         If .Cells(UserRow, SheetCol).Value = "Ð" Then
             Sheets(SheetNm).Unprotect "123"
             Sheets(SheetNm).Visible = xlSheetVisible
        End If
        If .Cells(UserRow, SheetCol).Value = "Ï" Then
             Sheets(SheetNm).Protect "123"
             Sheets(SheetNm).Visible = xlSheetVisible
        End If
        If .Cells(UserRow, SheetCol).Value = "x" Then Sheets(SheetNm).Visible = xlVeryHidden
        Next SheetCol
        Application.ScreenUpdating = True
End With
End Sub

First error..
For some strange reason the With Sheet17 command didn't help Cells(.Range) to know what sheet to go to. I had a solution, but it only worked until I made other needed changes (can't recall what they were), but alas, I was working on more solutions when it crashed.

So to fix error, before first crash, I finally added

Dim wks as worksheet
Set wks = Sheets("Security")
'and then further down edited the tough code as
Set Mycell = Cells(wks.Range("AdmUsrCk").Value, UserCol)

'Security' is the name of Sheet17. I believe that change worked. But only until I needed the Mycell - where I began the second crash.

Second error..
Mycell is the location of the UserName. The next column is the location of the Password. The code was supposed to put the value into "Security" on that cell. But instead it put the code into Sheets("Business"), the sheet that I started on. A VBA error code never came up. The code worked... except it saved to the wrong sheet. So I added a Dim for wk, and set that sheet equal to the Business Sheet. I may have used With Sheets("Security") as well. I was playing with the Immediate pane, and using that to try to figure out the issue... when it crashed.

I realize that Option Explicit may have helped. I have it on most modules, but since I borrowed 50% of the code on this sheet (not the part that caused the crash), I didn't think to add it.

It may also be pertinent to know the relationship between the two sheets. The Business sheet is like a start sheet, and on there I added the command button to open a UserForm: "frmLogin". The code for the Login is sent to "WBSecurity" module (code above). This module is located under Modules, not under the worksheet objects. The "Security" sheet is physically located beside the "Business" sheet if that matters.

My worksheet was autosaving to my OneDrive. Occasionally it would pause, and the tiny circle would run, that shows something is computing. When that circle is short, then everything works well. Both crashes began with that circle... something computing... then it gets stuck... the sheets go dim, and then it disappears and won't reopen.

It may have something to do with the protecting of sheets. I had that part working pretty smoothly... well, that is the borrowed code... each column representing a different sheet, and the three symbols in the table telling the code which of the three states to make that sheet.

Anyway, that's about all I can give you right now. Any ideas?

Thanks
Gary

[Aussiebear]

And all was working well before you tried the above code?

[garyj]

And all was working well before you tried the above code?

Well I was still in the final stages of development, so in that sense no. I had about 15 dynamic sheets working, the relationships, several queries, and the log in. When I first added the login system, there were about 3-4 issues that I set aside by turning the line of code into a comment. It worked fine without doing those things. But I needed to fix them as it left some ways for future problems, so I did, one at a time. And the last one was as described.

[garyj]

I’ve had issues with Excel sheets before.. typing into a cell and finding nothing in it after, finding the data in the next sheet later. This was without vba code too. I would then try again and it was repeatable. They only way I found to deal work it was to shut down the computer and restart. Maybe it’s a memory issue. Seeing it happen with code was puzzling indeed.

[Paul_Hossler]

Desk checking -- Try this

<dot>Cells(<nodot>Range ....

Not sure about the .Value on the Range (...) since it might depend on what AdmUsrCh is

    With Sheet17
          Set Mycell = .Cells(Range("AdmUsrCk").Value, UserCol)    '<----------------This was what crashed my first worksheet.

[garyj]

Thanks, Paul. That is possible, though I finally, after many attempts, managed to get the repaired copy open. The repair removes two ActiveX buttons. Perhaps I should use form buttons on the sheet and send the macro to the VBA? Anyway, on the repaired copy I see what had worked is this...

    Set wks = Sheets("Security")    With wks
        Set Mycell = Cells(wks.Range("AdmUsrCk").Value, UserCol)

That had worked... though I can see that Sheet17.Cells(Range... makes sense. Not sure how it relates to the problem of writing to the wrong sheet. By the way, AdmUsrCk is the named range of a single cell on Sheet17 (Security). It contains this formula.. =IFERROR(MATCH(B5,UserName,0)+4,"") B5 is linked to the frmLogin UserName that was just entered by a user.

I have a side question this morning. Since I finally opened the file, before I close it... I already exported each VBA Excel object and module. My practice was to Copy/Paste to Notepad, which only allows text, and then Copy/Paste back to the VBA. I heard somewhere that the Export, Import can get rid of issues that Excel generates behind the code... good to clean it up and start fresh. I am guessing I would need to delete the VBA after export, then save and close the spreadsheet, then open it and import the VBA. Is that correct? Do I need to also copy paste from spreadsheet to new spreadsheet? I would think the spreadsheets should be good as is, No?

Gary

[Paul_Hossler]

Not sure how it relates to the problem of writing to the wrong sheet

Even with the 'With Sheet17', without using <dot>Cells to refer to the specific parent object (i.e. Sheet17), just 'Cells' without the dot refers to the ActiveSheet, which may or may not be Sheet17

[garyj]

Ahh, I thought I was saying take the cells for the range in sheet17, but what it is interpreted as is the cells in the activesheet, and it’s .range which it may not connect to the with. I have seen examples of code where the dot occurs in more than one place on a line, always referencing back to the with command, but I do understand that if the first item stands alone then confusion reigns throughout. Would something like that if run through on various attempts be enough to cause a crash?

[Paul_Hossler]

Would something like that if run through on various attempts be enough to cause a crash?

This is Excel so it's hard to say

However, I've had good luck with ...

http://www.appspro.com/Utilities/CodeCleaner.htm

The Excel VBA Code Cleaner

During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files.

Unfortunately it only works with 32 bit Excel, but you can still manually export modules and then import them (I think) since that seems to be how the cleaning function works

I have seen examples of code where the dot occurs in more than one place on a line, always referencing back to the with command

Option Explicit

Sub test()
    With Worksheets("Sheet1")
        With .Cells(1, 1)
            MsgBox .Address
            With .Interior
                MsgBox .ColorIndex
            End With
        End With
    
        With .Shapes(1)
            MsgBox .Name
        End With
    
        MsgBox .Name
    
    End With
End Sub

[garyj]

Desk checking -- Try this

<dot>Cells(<nodot>Range ....

Not sure about the .Value on the Range (...) since it might depend on what AdmUsrCh is

    With Sheet17
          Set Mycell = .Cells(Range("AdmUsrCk").Value, UserCol)    '<----------------This was what crashed my first worksheet.

Yes, Paul. Finally had time to finish restoring the file, and yes, this worked. Just had to move a dot
Thanks

Gary

[garyj]

Also, Paul.. mine is 64bit, but I followed that link anyway and read it through. That is basically what I did.
Thanks

[garyj]

It just crashed again. Getting rid of the Active X OLE drop down. See if that helps. Trying to use a Macro based one instead.