Lock / Unlock Spreadsheet Userform
Sep 13, 2012
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
[Code] .........
View 1 Replies
ADVERTISEMENT
Aug 7, 2007
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?
View 3 Replies
View Related
Dec 10, 2006
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
View 6 Replies
View Related
May 29, 2012
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.
View 9 Replies
View Related
Oct 16, 2009
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?
View 9 Replies
View Related
Nov 6, 2006
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.
View 5 Replies
View Related
Jun 8, 2007
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.
View 3 Replies
View Related
Dec 20, 2007
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.
View 3 Replies
View Related
Jun 26, 2014
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.
View 1 Replies
View Related
Feb 25, 2009
I want cell g12 to be locked unless cell h7 is Grass Fire or Timber Fire (H7 is a drop down validated cell)
View 8 Replies
View Related
Apr 24, 2008
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.
View 9 Replies
View Related
May 15, 2013
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"!
View 1 Replies
View Related
Sep 12, 2013
How to Lock/Unlock an Excel Cell Based on the Contents of Another Cell?
View 1 Replies
View Related
Jan 14, 2013
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?
View 1 Replies
View Related
Jun 27, 2012
I have a excel worksheet that I am looking to market and I want to give the prospective customer a 30 day free trial before purchasing my worksheet. What I would like to do it be able to lock the worksheet so it can not be used after 30 days and also display a message telling them that the trial period has ended. I would also like to keep all formula cells locked at all times to prevent unathorized copying of my calculations.
View 2 Replies
View Related
May 28, 2009
I am having difficulty with a Windows Media Player (WMP) object in my spreadsheet. I go to design mode and set the desired size of the object in the spreadsheet; I then save and exit. Next time I open the file the object is much smaller. In fact every time I open the spreadsheet; save the spreadsheet and exit it gets smaller and smaller!
How I can LOCK the bugger in place - ideally by using the corner points of the cells - For example Top Left Corner of A1 to Bottom Right Corner of T36.
View 3 Replies
View Related
Feb 12, 2014
For each new case they have a spread sheet that they edit - I would like for this spreadsheet to lock a month after it is first edited/updated.
View 2 Replies
View Related
Jul 25, 2006
how do i show a userform in Excel in the spreadsheet environment? I used to "run macro" but unable to run userform code "private sub".
View 2 Replies
View Related
Oct 14, 2009
At the moment i've got a userform with 6 text boxes and what I need is when the 'save' button is pressed, all the words from the text boxes is put in 'customers' sheet. I've found this but I just adds data to the top of the current sheet.
View 12 Replies
View Related
Oct 22, 2007
i am trying to use this code to view the workbook (WK) in the userform named WK
it can not reconize .spreadWK
Private Sub UserForm_Initialize()
Me.SpreadWK.Cells.Range("A1:E10").Value = ThisWorkbook.Worksheets("WK").Range("A1:E10").Value
End Sub
however this is saying that it can not reconize
.spreadWK
View 9 Replies
View Related
Jan 5, 2008
I just want to know how do you copy details from a userform (Im doing patient details such as Name, address etc) in the spreadsheet. I have got an OK button I think a VBA code needs to be used that will tell the from to copy the details in an empty row. My user form currently overwrites the original row and is annoying!
View 3 Replies
View Related
Jun 13, 2013
I am working an a userform. I want people to add records to the spreadsheet thru the UserForm ONLY, meaning that if they go straight to the spreadsheet they wont be able to edit anything.
View 1 Replies
View Related
Jun 25, 2014
I have added a user form to this spreadsheet to make it a little more user friendly to edit/add/delete some information. Now the API used only needs longitude/latitude (lat/lon) to be input by the user. In this case it would be good that for each lat/lon to have a custom name added by the user.
How to link the userform, that's already made, to the information in the spreadsheet?
I was thinking it would be convenient to have the "Site Name" added to a column in the sheet named "Site List" and then have the "Fore Cast Data" pull the names from there.
View 13 Replies
View Related
Mar 19, 2014
excel macro through userform for spreadsheet reconciliation.Can we have userform for doing spreadsheet reconciliation(Only by using VLOOKUP). Here are the things i need in userform
1)First box that will ask you Lookup Value (column range)
2)Second Box will ask you Tabel_array say (column range)
3)Third box will ask you Col_index_num (column range)
4)Fourth box will ask for range_lookup (0,1)
5)Fifth box will ask for the column where you need your result in excel
View 13 Replies
View Related
Mar 5, 2007
I've got a 'Control Panel' spreadsheet which is all based on UserForms. When a user clicks a particular button, it opens one of many separate 'Regional' sheets which they work in.
When the user closes the 'Regional' sheet, I would like the 'Control Panel' sheets userforms to show again. However I can find no way of doing this as it simply switches back to the 'Control Panel' sheet without loading the forms (as I had to hide the 'Control Panel' useforms to give them access to the 'Regional' sheet).
View 9 Replies
View Related
May 25, 2009
I have a question related to Userform and/or Message box. In case a macro solve and displays the result as in Userform and Message box then is it possible to write (type) the values to the excel sheet without closing the Userform or Message box ?
View 2 Replies
View Related
Nov 18, 2007
I have created a userform with textboxes. I want to update cells on a spreadsheet with the values in the textboxes. I have set the code into a command button to update the values, but they only get updated when I close the userform, not when I click the command button.
View 9 Replies
View Related
May 24, 2006
I‘ve made a Userform which should transfer data form the userform to a spreadsheet (see Example). Because the spreadsheet contains a formula in column A the data must be place in column B and further. This part of the code is working. I also want the data to be placed at the first empty row. The following code works when no data or a formula is entered in column A. Because column A contains data up to row number 14 the data will be placed in the first empty row number 15.
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
' Find First empty row
lRow = ws. Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
The code has to be changed so it will find the first empty row. If I use the userform in this example the data must be place in row number 6 in the columns B till L.
View 3 Replies
View Related
Nov 15, 2006
I have a userform that allows the user to choose an order template from a combobox. I also have a ton of labels in the userform to return values based on the template chosen. then they can approve the template or choose another and the template chosen will be returned to the spreadsheet.
to see what they look like:
link to the userform (originally they were textboxes, but i changed to labels so the user cant change the values):
http://www.ppbcsingles.org/BRINKS/template-userform.jpg
link to the table:
http://www.ppbcsingles.org/BRINKS/template-values.jpg
to return the values to the labels based on the combobox... this is what i came up with, but it says that my sub or function isnt defined...and it highlights the error in "Private Sub ComboBox1_Change()"
Private Sub ComboBox1_Change()
Dim x As Integer
x = OrderTemplateSettings.ComboBox1.Text
Workbooks("newlocationsetup.xls").Worksheets("Templates ").Activate
'CURRENCY
OrderTemplateSettings.Label82.Value = HLookup(x, Range("A2:AE65").Value, 1, False) 'ones
OrderTemplateSettings.Label83.Value = HLookup(x, Range("A2:AE65").Value, 2, False) 'twos
OrderTemplateSettings.Label84.Value = HLookup(x, Range("A2:AE65").Value, 3, False) 'fives
OrderTemplateSettings.Label85.Value = HLookup(x, Range("A2:AE65").Value, 4, False) 'tens
OrderTemplateSettings.Label86.Value = HLookup(x, Range("A2:AE65").Value, 5, False) 'twenties
OrderTemplateSettings.Label87.Value = HLookup(x, Range("A2:AE65").Value, 6, False) 'fifties
OrderTemplateSettings.Label88.Value = HLookup(x, Range("A2:AE65").Value, 7, False) 'hundreds
View 2 Replies
View Related
Apr 12, 2007
I have come up with the idea to create a userform to act as a login page for my project. I have the project complete and need a way to protect it. I want the userform to display an input box asking the user to enter their 'login ID'. This has to be either "SUPERVISOR" or "STAFF". Or If I have a table of 'users' and 'passwords' in sheet (USERS) to match them against that if it is easier?
Upon correct entry i need it to then display another input box asking for a 'password'. This needs to be "SHOES" for both logins. If the login is not equal to SUPERVISOR or STAFF I need a message box displaying "username incorrect". and for it then to show the input box requesting the 'login ID' again. Again if the password is not equal to "SHOES" to display a message box displaying "password incorrect" Then show the input box requesting the password. Upon correct entry for both the username and password i would like my other userform to show. This is displayed by:
Load UserForm1
UserForm1.Show
I would like the correct 'username' to be displayed in cell "D27" on the active sheet and 'password' to be displayed in cell "F27" also on the active sheet To show who is 'Logged On'. If there is a way to display the password using asterix's for security that would be useful.
View 6 Replies
View Related
