Excel VBA - having issues with a worksheet being protected when it's not... 1

[SnayJ]

I have a workbook that's currently unprotected. The user would click a button and data would be transferred from one sheet to another... this worked fine. But for some reason it quit working. I noticed that the destination sheet was protected... so I deleted all the code that protected or unprotected all sheets. And now, when the user clicks the button, the data transfer still isn't working, but now when the destination sheet comes up (without the transferred data)... the sheet is Protected... but when I click in a cell, it becomes Unprotected.

The code I was using to Protect and Unprotect the the sheets is below...

ThisWorkbook.Sheets("Office View").Protect Password:=AdminPass
ThisWorkbook.Sheets("Office View").Unprotect Password:=AdminPass

Since all the Protect/Unprotect code has been removed....and no worksheets are protected...why would the sheet come up Protected only to be Unprotected when I click on any cell???

If you need to see the Workbook, I'll post it... just thought I was missing something simple. Remember, this used to work fine... then it just quit working. Just prior to it quitting on me, I had erased a bunch of code on the same page but nothing that had anything to do with the transfer code (which quit working).

 

[SkipVought]

Plz upload your wkbk.

How do you "know" that this code has been removed?

"...when I click on any cell..." sounds like an Event procedure in a sheet object code module.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SnayJ]

I deleted all the references where I applied a Protect or Unprotect code.

When you open the file, use "1" as the passcode. It will give you an admin login... and then click "View Transactions". That is supposed to take all the data from "Sales Datasheet" worksheet and transfer it to the "Office View" worksheet... it was working but now it's not. The only change I made prior to it quitting on me. Is I had code behind the option buttons at the top of the "Office View" worksheet as well as behind the dropdown... but I removed them because they weren't working. Once I did that... the code that transferred the data quit working.

Look on the module for "OFFICE_VIEWcmd_Click()"

Thanks for checking it out.

*Fair Warning*... I am not an Excel Programmer... I program mainly in Access VBA... and I've been looking up how to do things in Excel and cut/pasting and changing what I need to change. I'm also not a professional so my code is not super clean.

 

[SkipVought]

Well I opened your wbk, made the Office View visible and its not protected???

BTW, somewhat conversant with TREC as my wife was a RE about a decade ago. But lots of the Clear Fork under the bridge.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Well, stuff was being copied from the Sales Data sheet to Office View row 14,000+

Your ClearContents didn't go far enough. [highlight #FCE94F]This[/highlight] does...

Sub OFFICE_VIEWcmd_Click()
    
    '******************************************************************************************************'
    '*** Make Office View Worksheet Visible, clear contents and copy over all data from Sales Datasheet ***'
    '******************************************************************************************************'
    Sheets("Office View").Visible = True
    Sheets("Office View").Range("A3:[b][highlight #FCE94F]A999999").EntireRow[/highlight][/b].ClearContents
    Dim i, lastrow
    lastrow = Sheets("Sales Datasheet").Range("A" & Rows.Count).End(xlUp).Row
    For i = 3 To lastrow
        Sheets("Sales Datasheet").Cells(i, "A").EntireRow.Copy Destination:=Sheets("Office View").Range("A" & Sheets("Office View").Rows.Count).End(xlUp).Offset(1, 0)
    Next i
    Sheets("Office View").Select

End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SnayJ]

You know I never bothered scrolling that far down, just assumed it wasn't coming over because it normally did on line 3.

That did work...thanks so much. Now I have to figure a way to get those damn filters working. But I didn't want to use filters... I wanted to re-pull the data based on the filter options. I had an idea of how to do it... I'll try again tomorrow but if it doesn't work, I'll post it again. Thanks Skip!

 

[SkipVought]

I'd use MS Query to get the exact data you need in one operation. You can "filter" by specifying criteria for one or more fields.

Data > Get External Data > From Other Sources > From Microsoft Query > Excel Files*... and drill down to your workbook.
FAQ68-5829.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]