INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)

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...

CODE

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).

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)

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.

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

Your ClearContents didn't go far enough. This does...

CODE

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:A999999").EntireRow.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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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!

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

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: Using MS Query to get data from Excel.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close