Is it possible to lock/unlock the VBA code in a workbook through VBA from another workbook? I wish to copy modules from one workbook to another, both workbooks will have the VBA code password protected to stop prying eyes. Obviously I need to unlock the VBA in the target workbook before the copy and lock it again afterwards. Can this be done programatically? If there is no function in VBA to do this can you provide an example of how to do it using SendKeys if that is the only way to do it?
I have the unlock/lock code below but i cannot get it to unlock the correct spreadsheet. Where do i need to place it in the code so that it will work properly.
Code to unlock:
Public Sub unlocksheet() 'unlock the worksheet On Error Resume Next ActiveSheet.Unprotect Password:="test"
Within this code:
Private Sub cmdSub_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("AFW") 'find first empty row in database
None of the information Ive found on the forums seem to work in my sheet and Im not sure why. After protecting my sheet/workbook, I would like a particular input of a cell to Unlock another cell.
ie if the contents of cell A1 is the phrase "TRUE", then cell B1 will be Unlocked for editing. If the contents of A1 is "FALSE", then B1 shall remain locked. From what i can tell, this must be carried out using VBA code. If anyone can resolve this issue, Id be also very grateful if you could explain what each line of the code means
Is there a way to lock and unlock multiple excel spread sheets with only one password at one go? For example if I have 5 sheets, I would like to be able to lock and unlock Sheets 2-5 with one password at one go while leaving sheet 1 unprotected. Do not want to type a pwd for each sheet.
I have a worksheet that, unless a form for data entry is activated, should be read-only for columns 'A' thru 'M' and 'O' with column 'N' left editable.
The form is activated by a button with a macro assigned to enter the data in the above columns when the form is closed. This has been compiled and works nicely with the sheet unlocked, however, as i need certain columns to be read-only unless the form is activated, i'm stuck with leaving the whole sheet unlocked for editing with or without the form being activated.
Is there any way a VBA code could be used to unlock the worksheet columns that are read-only when the form is activated, and then lock them once the form data is entered to the worksheet and the form closed?
I need to lock cells or ranges in a worksheet which has value (any value).... The cells which are blank should be unlocked so that the users can enter data.
I need to lock certain cells on a worksheet when a value is selected from a validation list. For example:
user goes to select an option from a validation list, that cell is now locked.
User decides to change the value with a blank option, available on the same list. the cell becomes unlocked ready for new input from the list.
the cell can only allow input when the value in the list matches that of another cell. if another cell already has different data in, it wont allow you to change the value.
trying to put together a formula, I am trying enable two cells to change between being 'Locked' and 'Unlocked'. I am doing this by using a CheckBox which is referenced to cell [E16] so that when it is checked, 'TRUE' will be displayed and when Unchecked 'FALSE' is displayed. From this I have tried to devise a formula for the cells [c26:I26] and [K26] that when cell [E16] shows 'TRUE' the cells [c26:I26] and [K26] are Unlocked and when it shows 'FALSE' the cells [c26:I26] and [K26] then become locked.
I want to make break tracker. When i press Windows + L Or CTRL + ALT + DELETE (Lock computer) Automatically lock time upload in my excel sheet. Then when i unlock pc then automatically unlock time will upload in my excel sheet.
I'm looking for a solution to a problem and from what I've read in the forums so far, this is the place to come. I first want to say that I have tried to solve the problem by using guidance from other similar threads and adapt those answers to my needs, but as my understanding of VBA is non-existent this was somewhat difficult. I don't want to post a repeat question and I hate wasting people's time if unnecessary, but I can't do this on my own.
That being said, here is the problem;
(I have attached a simplified copy of the worksheet I am working on for reference)
I would like to lock a particular cell in my spreadsheet based upon the selections made from two drop down validation lists (Cells “C4” & “D4”)
Essentially, if a correct choice is made in both, then the cell “E4” becomes unlocked for editing. If however, the correct selection is made in one list, but not the other, I need the cell “E4” to remain locked and a message to appear to prompt the user to correct their mistake.
Furthermore, both the validation lists have a whole range of selections that are very similar but have subtle differences;
E.g.
"Soft Eye by Talurit" "Soft Eye by Splice"
Is it possible to write a VBA code that performs a similar function to the =IF(ISNUMBER(SEARCH("**",))) formula, so that if any choice that contains the word "Soft" is selected from either or both lists then the cell “E4” becomes unlocked, but if the selection does not contain that word then the cell remains locked?
If anyone could provide a code to make this happen, I would be very grateful! But please explain how to do this, as the word “Soft” won’t be the only parameter and I may need to alter the code so I need to understand the principle behind it; For example, on the spreadsheet, if in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Soft Eye’ is selected then cell E4 is unlocked for editing. If in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Fused and Tapered’ is selected then cell E4 is unlocked for editing, but if in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Standard Eye’ is selected then cell E4 remains locked and an error message appears to alert the user that his/her choices are incorrect. If however, "Soft" is not selected in either list then cell E4 remains locked (it has to be selected in at least one of the two lists to allow editing).
This is a work in progress (not all the formulas that will eventually be used have been entered yet, so please excuse the untidiness of it all) and the choices are likely to change based on input from my colleagues, so I need to understand how the VBA works in order to alter it accordingly, but I don’t want to put any more work into this (it’s still in the early stages) if it isn’t possible to achieve these needs.
I am looking to write a code to lock/unlock cells based on values selected using a drop down list (see attached Excel) For example if Netherlands is chosen as the Country (Column A), then except for the columns Amsterdam (column B) and Eindhoven (column C), all other columns must be locked. Similarly, if India is chosen as the country from the drop down list, then only the columns New Delhi and Mumbai must be editable for the user, the rest of the columns remain locked.
I tried tweaking some of the lock/unlock codes around, but got lost especially with getting to run the macro - still trying to come to terms with running a "Private Sub Worksheet_SelectionChange" function in the sheet from "Module"!
I'm trying to write a vba code that does the following....
There is a question in column A to which the user chooses yes or no from column B. Based on the response in column B, I'd like the same row column D to be formatted so that if the anser is yes, the cell is white, and unlocked. If the response is no, the cell is cleared, locked and the greyed out. (e.g. if B4 is "Yes", the format in D4 will change) So far I've come up with the following which formats the colour:
Dim response As Range For Each response In Range("$C$10:$C$73") If response.Value = "Yes" Then
response.Select ActiveCell.Offset(0, 2).Range("A1").Select With Selection.Interior
[Code] .......
How to add in a .clearcontents function, so that the contents are cleared if the response is not "yes", and also what I would need to add to unlock the cell in column D?
Until recently I successfully used this code to navigate to a web page and populate some of it's boxes, before programatically pressing the page's "Validate" button, whereupon the page would test the data I'd populated the boxes with, and come back with an answer to tell me if there were any errors in my data:
Private Sub CommandButton1_Click()
Dim uf1_cbutt1_click_ie As Object
Set uf1_cbutt1_click_ie = CreateObject("InternetExplorer.Application")
Can anyone shed any light on how I may be able to programatically navigate with VBA to the page I want? I'm also having trouble finding the new name of the "Validate" button - and how to programatically click it.
I have the following function that looks at a range of data and deletes duplicate entries in each column. I want to change the range that is examined programmatically, rather than having to retype it for each range in this huge sheet! I have subtotaled and counted the rows in each range, so I know how many there are, but I'm stumped as to how to increment teh range correctly. I'm attaching a small sample of my data. The full sheet is 30,000+ rows and 94 columns.
Sub DelDups2() Dim rngSrc As Range Dim NumRows As Integer Dim NumCols As Integer Dim ThisRow As Integer Dim ThatRow As Integer Dim ThisCol As Integer Dim J As Integer, K As Integer, x As Integer Set rngSrc = ActiveSheet.Range("c68", "cr70") NumCols = 94 NumRows = rngSrc.Rows.Count ThisRow = rngSrc.Row ThatRow = ThisRow + NumRows - 1 ThisCol = rngSrc.Column For x = 1 To NumCols For J = ThisRow To (ThatRow - 1) If Cells(J, ThisCol) > "" Then................
I have a VBA procedure that I need to be able to link/attach to an excel file which is created on the fly, and I need to do this programatically / on the fly. Basically, currently from my front end app, I output the necessary data to excel, and then format that data in excel using vba code sent through OLE to excel. Right after the formatting is done, I would like to then send the necessary vba commands to excel that would take my existing VBA sub routine, link it somehow to my excel file (by inserting it as a macro?, by registering/linking it as a .bas file?, any other options/ideas?), and then give the user a button or shortcut in the excel file that will run that VBA sub routine.
The underlined part above is my main goal here, and I already have the rest covered. I know there is more than one solution for this, but I would prefer a solution that allows me to store the vba code in the excel file itself, so that I only need to distribute the .xls file whenever I want to deploy this spreadsheet somewhere new.
I want to use script to programmatically create Hyperlink in multiple cells. The Hyperlink will trigger a macro event and run a simple command using the "PC Name" as input variable. The Hyperlink should have:
-Text to display: - different "PC Name" in each cell
-Type the cell reference: - corresponding to the cell where the Hyperlink is inserted
-Link to: - Place in This Document
I tried using cell formula, but it always default to "Existing File or Web Page".
I have an expanding worksheet and would like to move a button so that it is always near the bottom of the worksheet data. When the button is pressed it reworks the worksheet and adds in some additional rows (up to 10 max). I'd like it so that the procedure also moves the button down keeping it in line with approximately the last row of data but not sure how to do this with vba...
Trying to select multiple shapes based on shape properties. The missing piece is the code to select the shapes. The examples in the help use the 'Array' function with a list of shape names.
Is there a way to select a random set of shapes without using the Array function?
Or how does one build a list for use with the Array function?
I'm trying to programmatically add and remove the Outlook 11.0 Reference Library in Excel 2003. So far, I have found the following code which successfuly adds the reference:
To refresh a UserForm ListBox when new items are added, I Unload and Show the Userform as part of the procedure.
Code: Private Sub CommandButton4_Click() 'do stuff 'do some more stuff 'do a few more things 'finish doing stuff Unload Me UserForm2.Show This returns the UserForm to its Initialized state and displays the refreshed list.
But, this requires the user to click CommandButton6 to continue adding additional items. If CommandButton4 code could click CommandButton6, the UserForm would Show in its "add item" state...I think. Is there a way to programmatically click a CommandButton?
I've already tried 8,321 ways without success...(OK, maybe only 4 ways)
I have a workbook that contains a form with cells that use choice list validation. It also contains a worksheet with all the choice lists. Each choice list is a named range. The form is used to collect data that is new and from historical records. The historical records contain entries that are no longer allowed in new data. To allow for the older data I have choice lists (named ranges) that contain both the new and obsolete choices. This is a data problem waiting to happen. I would like to be able to have the user tell the workbook if the data is new or historical. If new I would only provide them with current choices. If historical, I would provide them with all choices, current and obsolete.
I would link the VBA script to change expand the ranges when told the data being entered is historical. The problem is I don't know how to code such a script. For example one of the cells has as current choices W, M, D. The range would be named MoistureState and the range would be A2:A4. There is one obsolete choice, E. If the Historical data button was clicked the MoistureState range would change from A2:A4 to A2:A5.
What I have is a list of data on a sheet. Through some code, I would like to visualize my data in a certain way. What I currently have is a grid, each square corresponding to a single data point. I would like to assign a tooltip to a label that will be sitting on top each of these squares.
What I have so far: I have a userform which will be populated by labels programmatically. I am doing it like this because there are at least a few thousand labels to be created. I am currently doing it like this:
Code: counter = 15 For i = 1 To 20 For j = 1 To numrows
[Code]....
Essentially, what I have is a grid, and I would like to change the letter "x" in the label to another color when the mouse is hovering over the label.
Is this the correct way of achieving what I want, or is there a better way? Is there a way of assigning a single mouseover/hover event to the label once it has been created? (All these labels can be handled by a single function to just change the text color).
I am trying to create a table (listobject) from a textfile datasource on a shared drive.
How to get this over the line, I have the following code which I am struggling to finish.
Code: Sub Testing Dim wb As Workbook Dim ws As Worksheet Dim ws2 As Worksheet Dim lo As ListObject Const Pathway As String = "F:Scripting" Const File As String = "Log.txt"
I have a suite of subroutines and functions stored in an .xla file, which other users have access to. It's installed on the local machine via macro which installs a user defined menu to access them from. Howerver, when an XL chart worksheet is active, this menu doesn't appear and there are a couple of functions where it would be useful to have access to when viewing such a chart. I've noticed user defined toolbar icons are available in this view, so would like to use these to access the relevant routines. I know you can insert them in code (macro recorder) but is it possible to assign macros to them from the same code? Alternatively, is it possible to show my user defined menu in chart sheets as well as normal worksheets?
Suppose I have a userform named myUserForm and a checkbox on it named myCheckBox. How on earth do I remove myCheckBox from myUserForm at run time? I've tried every which way I can think and I always get "error 444". Some things I've tried are:
