Test If VBA/VBE Project Is Locked

May 17, 2008

Using vba, I want to be able to tell if the vba code is locked. Using the below function I can tell most of the time. The only problem is if the " Lock project for viewing" isn't checked the below function will say it isn't protected. Is there a way to tell if it is protected even if they didn't check "Lock project for viewing".

Function ProtectedVBProject(ByVal wb As Workbook) As Boolean
' returns TRUE if the VB project in the active document is protected
Dim VBC As Integer
VBC = -1
On Error Resume Next
VBC = wb.VBProject.VBComponents.Count
On Error Goto 0
If VBC = -1 Then
ProtectedVBProject = True
Else
ProtectedVBProject = False
End If
End Function

View 3 Replies


ADVERTISEMENT

Locked Project Prompts For Password

Nov 13, 2006

I have a workbook with extensive code of wich the VBE project is locked for viewing. Since i have distributed the workbook i had some users that gets prompt for a password when trying to run some code trough a button on a sheet. I have tried recreating this error by increasing the macro security level and unticking "trust access to vbe project" since i thought that is where the problem lies.

But i had no luck in recreating the error. I came accross some posts that had a similar error when you exit/close the workbook, wich was a problem with google desktop search being installed on the machine. Any ideas on why the user is being asked for a password ?, the code that should run does NOT try to alter/change anything in the code/project itself, so that shouldnt be the problem, but i can't think of anything else.

View 2 Replies View Related

Generating List Of Project Parts Based On Project Duration And Time Window

Aug 4, 2014

I have a table of projects with 1) duration in year, 2) time window (number of years of our planning cycle), and 3) start year of the project. I want to generate a list of project parts of all projects where they may take place. This will serve as an input to an optimization program.

So a project of 2-year duration should have 2 parts over any year within the time window. I am including the "impossible" ones for my developer to tag them as "0" when we run it through his code.

View 5 Replies View Related

Consolidate 4 Excel Project Lists (Workbooks) To New Master Project List Using VBA

Sep 5, 2013

My task is to consolidate 4 Excel Project Lists (Workbooks) to a Master Workbook. The Project Lists has a different structure and almost different content. The relevant information is always on Sheet1 but it has completely different ranges. The only constant is the Project Number, which should be used to sort the information. Every Project should be listed only once with all the existing information.

I found a code written by Ron de Bruin which has already some components that I want to have in my VBA but I think there are still a lot of necessary adjustments to do.

Code:
Sub MergeSelectedWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim SelectedFiles() As Variant
Dim NRow As Long
Dim FileName As String
Dim NFile As Long
Dim WorkBk As Workbook

[code]....

The Master Project List should has the headers in Row1 and the information listed below. The Macro should automatically places the correct information to the correct column. Some of the information are in 2 or more of the lists but they should be listed only once in the Master List.

Project Number

Project Description
...
1111E.000000001

[code]....

I guess a problem is that the structures of the Lists are quite different so there must be a kind of sorting process.

In the end I want to have an Excel File with the Macro and a Command Button and by clicking the Macro creates a new Workbook with the Master List.

It would be better if there is a variable range instead of a defined. Like the Macro searches the last row and starts at this row and column.

View 4 Replies View Related

Get Excel To Open MS Project File And Then Run Project Macro On It

Jul 30, 2013

I am trying to get excel to open a MS Project file and then run a project macro on it.

What is happen is that when I run my code it works, and then excels VBA window freezes up and the run arrow goes away, and if I try to open project I just get a chiming noise and it won't open.

For now I have to select the MS Project file from a directory, but in the future I would like it to go through the folder and open all of the file in the folder

EXCEL CODE
Sub ImportMSProject()
Dim FileToOpen
Dim mpApp As MSProject.Application
Dim prjmacro As Object
'Identify the File to Open - START
FileToOpen = Application.GetOpenFilename("Microsoft Project Files (*.mpp), *.mpp")
If FileToOpen = False Then
Exit Sub
End If
'Identify the File to Open - END

[code].....

View 9 Replies View Related

Test Two Cells And Set A Third Cell With Different Values Depending On Test Results

Aug 21, 2013

I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.

A1 represent a number of years and B1 represent gains or losses (negative)in dolars.

The way I see the logic is as follows:

If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)

If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.

On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.

I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:

If A1 = 0 then C1 = NGL
If B1 = 0 then C1 = NGL
If A1 >= 1 and B1 > 0 the C1 = LTG.
If A1 >= 1 and B1 < 0 the C1 = LTL.
If A1 < 1 and B1 > 0 then C1 = STG
If A1 < 1 and B1 < 0 then C1 = STL

View 3 Replies View Related

Protect VBA Project Or Lock Project For Viewing By VBA

Apr 27, 2007

I have tried to record macro to protect VBAProject / lock project for viewing. But can not success by that way.

View 9 Replies View Related

Tally Report Of X: Count On Column B (project $) Depending On The Criteria On Column A (project Description)

Aug 28, 2007

I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].

View 4 Replies View Related

Cursor Locked Up

Dec 18, 2008

I try to move the cursor using the keyboard arrows it stays on the cell it is on and the whole spreadsheet page moves across the screen in the direction of the arrow. So how do I restore cursor movement to the arrows.

View 3 Replies View Related

View Locked Add-Ins

Sep 18, 2002

In keeping with their monopoly on code, Microsoft has, of course, locked all of the included XL add-ins (the code part).

I was interested in looking at how some of these add-ins functioned, seems like it could be fairly educational.

how to unlock the modules that come with XL's add-ins?

View 9 Replies View Related

How To Filter With Locked Column

Jul 30, 2014

I have a spreasheet with one locked collum that I don't want to be edited. Is there any way of filtering while this collum is locked? It seems to be that once it locks the collum it also locks the filters?

View 6 Replies View Related

Allow A Locked Formula To Recalculate?

Apr 30, 2009

how to lock a formula in a cell, protect the sheet but still allow it to recalculate when new data is entered in a unlocked cell that feeds the formula? I am using Excel 2003.

View 5 Replies View Related

Jumping Locked Cells

Oct 21, 2008

kindly help in jumping locked cell in a proteced worksheet..

View 9 Replies View Related

Check Locked Cells

Nov 6, 2008

Sub PastSpec()
If Selection.Locked = True Then Exit Sub
On Error GoTo ErrHan
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ErrHan:
Exit Sub
End Sub
I recorded part of it and adapted it afterwards. You will notice I have a line to check for locked cells, this does its job if the cell selected is locked. However if the cell selected is unlocked and the cell below is locked the code still excutes and pastes.

View 9 Replies View Related

Personal.xls Locked For Editing

Dec 9, 2006

I recently added two excel files to my startup, I receive the file in use error that the personal.xls is locked for editing box when the second file starts to execute. I can open them separately using their icons with no problem, what's the problem with opening them using the startup function. XP professional version 2002 SP2.

View 9 Replies View Related

Forecolour Options Are Locked

May 18, 2007

In the Properties for every Text box I have on my forms it's not letting me change any of the colours but I can change the fonts etc, I'm using Excel 2003.

View 3 Replies View Related

Run Macro From Another Workbook Locked

Feb 2, 2008

I have one spreadsheet that is locked and I cannot modify, with a button I want to press from a fresh new one that I have created.

The first one is private and cannot modify it.

Now, what code should I use in mine so I can "click" the button of the other one when I press it?

View 5 Replies View Related

Can't Click Hyperlink In Locked Cells

Jun 10, 2013

Any way to make it so that a cell can remain locked but be able to click a hyperlink? I need the cell to remained locked so nobody can alter the hyperlink name and value but I would like users to be able to click it and have it open in their browser.

View 9 Replies View Related

Clearing All Tabs - Editing Locked?

Mar 20, 2013

I have an application that I made for a company. the editing is locked on each tab except for the blanks for people to either check mark a box or fill in the space provided for an answer to each question. There are around 30 tabs. Sometimes i open already completed applications since most of the answerer will be the same. My question is. is there a way to select say 6 tabs and clear out all the data that someone had typed in (check marks, answers in space provided?)

View 4 Replies View Related

How To Copy A Locked Cell Without Formula

May 13, 2013

I have created a spreadsheet that will be viewed/used by various users (read only as I have protected the sheet except from three fields), although I would like to give them the ability to copy the contents of a specific cell that is protected.

I have tried to give them the ability to select the locked cells and then copy, but that means that the formulae in the cell is copied as well.

how I can let people copy a locked cell without the formulae?

View 2 Replies View Related

Locked Cells Have Changed Formatting

Jan 23, 2014

On an excel document that was originally formatted as a number and then locked, somehow users have changed the formatting to a date. I think it is an accident, but I can't figure out how they are doing it. I have several data files on a server where several offices have access to them to input data and somehow formatting gets changed sometimes, even when locked.

View 3 Replies View Related

Entering Text In A Locked Cell

May 29, 2014

I have worksheet that contain values in column E and G and the total in column K. E and G are unlocked for manual input and column K contains the formula for the total and is locked. From time to time a certain row will not be applicable and before I started locking the cells in column K i would just put "not applicable". Now that it's lock I can't do that. My only option now is to put O in column E and G but I would rather put "not applicable" in column K. Is there a way around this. I was thinking of something along the line of a macro that would unlock the worksheet input the text and re lock it. The macro would have to lunch when a locked cell is selected or have a button that would launch the macro an would prompt for which cell the text would be entered. Is this possible.

View 7 Replies View Related

VBA Locked And Unable To Even Get To A Place To Unlock?

Jul 30, 2014

Some how my spread sheet became shared and has been locked. so when I went to use my VBA to unlock the sheet i found my project locked as well and no way to get to properties to try and unlock.

View 3 Replies View Related

Macro Is Unlocking Locked Cells

May 7, 2008

I'm using three different workbooks for the macro I've designed. The macro works exactly as I planned it would, but I'm getting a curious side-effect of running the macro. I have over 5,000 workbooks that I need to open, unprotect, change (mostly by copy/paste), and reprotect. It's a fairly straight forward macro, but it is my first, and I must be overlooking something. When the macro is complete, cells that were not modified via the macro in any planned/anticipated way are being unlocked. This has left me puzzled and frustrated. The files are opened, unprotected, the cells are copied over as planned, reprotected, and closed/saved. Everything works perfectly, but the cells in parts of the workbook (Filename) (which were locked before the macro ran) have been unlocked and are no longer protected when the workbook is reprotected. Again, these cells were not within the ranges modified, and I'd rather not have to format every cell in every one of the 5 sheets of (Filename) to be locked.

File names are:
"Finished Goods Inventory TREE (ToDMSI).xls" (alias: Workfile - sheet providing file path to be updated)
"Random_workbook_selected_from_previous_filename.xls" (alias: FileName - the copy-to file being updated)
"MACRO TEST BOM Master.xls" (the copy-from file always open)

Comma Delimited table layout in "Finished Goods Inventory TREE (ToDMSI).xls":
Col A, Col B, Col C, Col D
Customer ID,Item Code,File Name, Directory
4FRE01,4FRE01-0001,4FRE01-0001.xls,4FRONT

Here's the

Public FileName

Public Workfile

Public ItemCode

Public CustCode

Sub OpenBOMSeq()

View 14 Replies View Related

Locked Cells With VBA And Ability To Filter

Oct 23, 2008

how to create two buttons so that I can have a data entry specific protection and then a lock all button. Here's the link to the thread:

http://www.excelforum.com/excel-prog...-a-button.html

And here's the code I'm currently using:

View 3 Replies View Related

Select Locked Cells In 2000

Jun 15, 2009

I've created quite a few workbook/worksheets in excel 2007 compatibility mode that are data entry intensive. When protecting these worksheets, you have the option of allowing the user to "select locked cells". I invariably uncheck this option because the user doesn't need to interact with these cells.

However, several of my users are running Excel 2000 and the same option is not available when one protects a worksheet in Excel 2000. Is there a way to do that in VBA for these Excel 2000 users such that when they click on a locked cell, the cursor will not respond to that cell?

View 5 Replies View Related

Sorting Data With Locked Columns

Feb 20, 2009

I have a spreadsheet on lets say 10 columns, now col 1 and 9 are locked so they cant be selected. Col 2-8 can be sorted by selecting them, but information in col 10 does not sort.

Question is there a way of connecting the cells in col 10 to the cell in lets say col 2, so thay when you sort the rows and cols between 2 and 8, the cells on col 10 are sorted as well As i have noticed you cant sort two differenr selections...???

View 2 Replies View Related

Personal XLSB Locked For Editing?

Dec 8, 2011

I have a simple macro that's in my default Excel startup file (Personal.xlsb - located in the folder C:Documents and SettingsUsernameApplication DataMicrosoftExcelXLSTART).

The problem is when I try to open two instances of Excel (not create a new workbook). I get the error message "Personal.XLSB is locked for editing."

I've look at other threads that talk about adding the macro as an "Add-In" (Personal.xlsb and multiple Excel's), BUT my problem is that I want to assign the macro a keyboard shoutcut (currently set to ctrl + shift + v).

I don't know of a way to assign an add-in macro a keyboard shortcut.

View 7 Replies View Related

Textbox In A Graph Locked To A Point?

Feb 15, 2012

I want to insert a text box into a line graph. I want the box locked onto a data point in this graph. The problem I am having is the graph is updated daily with more data so the point moves so I have to drag the box daily. Is there a way to fix this so it works automatically?

View 1 Replies View Related

File Locked For Editing - How To Unlock

Mar 15, 2012

I am getting a message, the Excel file i am opening (xlsm) is locked for editing by 'another user'. But, NOT SO, of course; i am the only user logged in to this PC, and there is no network access. I quit Excel and verified that i see no Excel processes running. But, when i try to open the file, i still get the "locked by 'another user'" message, and at this point can only open read-only. I looked in the folder the file is in and do not see any of those ~ files or anything else suspicious.

So, what do i do? How do i "unlock"? How can i open this file once again to make modifications to it?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved