I had posted a similar question on 10/27 but the thread has mysteriously diappeared. So, I am reposting my question. What I am trying to do is probably easily done using VBA. See attached workbook example.xls to follow along.
I have a dropdown (YES/NO) selection in Column C. I want to do the following when 'YES' is the selection for any row:
1) do a vlookup into the 'data' tab and return corresponding value in Column D
2) Lock the cell after the value is returned into Column D
If the selection is 'NO' then do nothing., ie., leave the cell blank and unlocked.
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 using the code below to lock certain cells depending on the value of other cells. The code below deals with one line of my spreadsheet only and as the spreadsheet comprises 38 data entry rows I've repeated this code 38 times in the worksheet module with the appropriate changes to row numbers.
It works, but causes much screen flickering and "thinking". I'm new to vba so no doubt I've made this code too extensive or lengthy or whatever (or just plain wrong).
Can anyone assist with suggestions on how to simplify the code and/or help with code to handle all 38 lines without repeating the routine 38 times?
I am trying to lock the unused cells in 32, 2 column by 7 row named ranges, based on whether or not two cells, above each range are equal or less than each other. In other words while one of the cells is less than or equal to the second cell all cells in the range below should be unlocked, as soon as that condition is no longer true the blank cells need to be locked.
I am trying to use this in the Workbook_Sheetcalculate so that the macro will run automatically.
I have a spreadsheet where there are two columns for the user to enter data in, but they can only select one column. I want to lock the second cell if data is entered in the first. I can't find a way from validation to do this. Is there another way via VBA (stupid question, I know... VBA does everything but scratch your butt)
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.
Trying to incorporate the following in a worksheet: Lock the cells automatically in the columns that are older than 2 days or more Lock the cells corresponding to today and yesterday using a command button after entering X Copy the sheet multiple times with the same features
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 have created a spreadsheet which I am trying to set up to allow users to select only 1 cell in a range and lock the rest. Each cell D5 to I5 has an assigned number 0 to 5. What I am trying to achieve is when a user clicks on a cell, the value of that cell is displayed and the remaining cells are locked. e.g. if the user selects G6, 3 is displayed and cells D6,E6,F6 and I6 are locked. The code which I am using is giving strange and unpredictable results.
Private Sub Worksheet_Change(ByVal Target As Range) If [D5] = "0" Then ActiveSheet.Unprotect ("PASSWORD") [I5].Locked = True ActiveSheet.Protect ("PASSWORD") 'Remove locked property if D5's value is anything else or is deleted. Else ActiveSheet.Unprotect ("PASSWORD") [I5].Locked = False 'Optional, reprotect sheet ActiveSheet.Protect ("PASSWORD") End If............................
Column C and D are should be picked from a dropdown list (data is in named range on sheet 3).
The choices that have t become available in a dropdown list in column E (values also in named rnage on sheet 3) should be based on the combination of column C and D.
The choices that have t become available in a dropdown list in column H (values also in named rnage on sheet 3) should be based on the value of column C.
I'm not sure the way it is set up now (as far as it is setup) is the way to go.
The file is attached here. I would like to be able to autopopulate cells based on a dropdown. The first tab has the final report and the other 2 tabs are the raw files.
I want to be able to pick a word from a drop down list and for related information to appear in the next cell. I just don't know the function to do it.
Here's an example: I have a list of first names and second names in adjacent columns. Excel question 1.jpg
In the 2nd sheet I have a drop down list of the first names:
Excel question 2.jpg
Is there a function to make the corresponding 2nd name appear in B1 when a first name is selected. I've seen examples where INDIRECT is used to create a dependent drop down list in B1 but I'd like the information to automatically appear.
I have a dropdown list (C1 on the dashboard sheet). When January is chosen in that list I want the value in D15 on the dashboard sheet to match the value in B13 on the Target sheet...and then the same for D16 on the dashboard sheet and B13 on the Actual Sheet. When February is chosen the values should match for the month and so on.
Essentially I want the drop down list selection to populate the right numbers based on the month chosen.
I am trying to create a spreadsheet which allows me to select an antibiotic from a drop-down menu (refer to cell D1) on Sheet 1.jpg, which would then color the cells below based on the color-coded coverage based on another sheet (refer to D2-D17 of Sheet 2.jpg).
I am trying to use a row of cells as update cells, where the user inputs an amount into greyed out cells, which in turn updates Sheet1!$I$2:$I$11, from which it will update other worksheets that are currently in progress. but I do not know how to go about it. Is it possible?
For each amount the user enters (for each Code Number) a date will be displayed below the amount.
I have a sheet where I have a "Status" column in each row which could be "Waiting for Band", "Waiting for Organizer", or "Ready to Go!". These options are set as a data validation list. I have 2 cells later in each row where the response can only be "Yes" or "No". I want for the Status column to be set automatically based on the responses in the later two cells. So for example
where C3 and M3 are Yes, the status column in B3 should read "Ready to Go!" where C3 is no but M3 is yes, the status column should read "Waiting for Organizer" where C3 is no and M3 is no, the status column should read "Waiting for Band"
The above is actually an exhaustive list of all of the potential outcomes(!)
Trying to create a dynamic costing spreadsheet, where the user has to make multiple selections from many dropdown lists, all of which end up showing a total cost.
So far I have managed to create relatively dynamic lists, so that the choice in one cell will dictate what options are available in the next.
But if a user selects an options form all the dropdowns, and then decides to go back and modify a previous option to something else, the effected dropdown lists do not change until you click on the drop down arrow again and see the options available have changed.
Let me try and simplify... 'Menu 1' has two option: 'A' and 'B'. If 'A' is chosen, then the next menu, 'Menu 2', will have options '1' and '2'. If 'B' is chosen from 'Menu 1', then the options in 'Menu 2' will be '3' and '4'. Lets say the user chose 'A' from 'Menu 1' and then '1' from 'Menu 2'... AND THEN went back and changed their choice in 'Menu 1' to 'B'.... 'Menu 2' will still be displaying '1', even though the options are only '3' and '4'.
Can I make the dependant menu's either reset or blank or highlight to show that a change made somewhere else has made them currently invalid
I have two sheets that I am using. sheet 2 has a list of experiments in column C with information regarding the experiment in columns F - J. On sheet 1 I have a dropdown box with a list of all the experiments. When I select an experiment from the dropdown box I would like to populate columns K - O on sheet 1 with the information from columns F - J for the selected experiment on sheet 2.
I need to make a dropdown list (I know how to do that) and based on that selection, I need some cells below to automatically complete. How can I do that? Is it possible with or without VBA?
I have a list of Names that has numbers accumulated over months. I am looking for a formula to use in a search tool that uses a drop down list to identify the name I am looking for and then the formula will show me the increase that happened in certain months.
See the attachment the result is coming from Denominators tab, I want the answer displayed in the Display tab, you will see a drop down list at C3 in Display, I am trying to show the answer in cell D9-I9.
how many the denominator has increased in that month. so for D9 it will come from denominator tab column c minus b, E9 will be d minus c
I've created seven names in my data validation source list on Sheet 3 in the following range of cells, A2-A7. My data drop down validation list is located on Sheet 1, A2 in the same workbook. I would like to trigger a macro based on the name I select in this drop validation drop down list that will fill out an address, telephone number, and email address in the following cells next to my data validation drop down list on Sheet 1: B2, B3, and B4. I want it to be designed so that users will select a name and have that person's corresponding address, telephone number, and email address fill right next to their name on the same sheet.
I have a workbook that has two worksheets. One worksheet is an input worksheet. A user will select a date from a drop-down list and type in the events that occurred on that date in 8 adjacent cells. The user selects a button that advances date and clears form. On sheet 2, whatever was typed into sheet one is saved via VLOOKUP formula. However, if I try to change something retroactively and select a previous date from the drop-down list, it clears everything in sheet 2 that was typed for any selected day.
First sheet has a client name and several cells of data along the same line. All of the data on this sheet is kept long term.
The second sheet is completed copied and deleted. In completing the second sheet the client name is selected of a drop down list and pulled from sheet one.
What I want to do and not sure if there is a way is that when I select a client and populate it on sheet two I would like the related data on that clients line from sheet one to populate to same named lines on sheet two.
if this is possible and if so provide a brief description of how it would work
I have a multisheet excel workbook that I am programming. I was wondering if there is a way to lock a cell on one sheet after the initial value was put in, allowing me to change it later. This may not be so clear so here is the problem....
I have Workbook1. The input from cell A3 on Sheet1 is 5. I have made a second sheet called Order Form. In cell B5 of Order form I have made it ='Sheet1'!A3. I am looking for a way to be able to change cell A3 on Sheet1, but keep the 5 on Order Form.
I need to have a current row password protected when the value in ActiveCell.Offset(0, 0).Value = "Close" but just to lock that current row. What I have is a close button on a userform that changes the ActiveCell.Offset(0, 0).Value = "Close", i just need to add another line of code to lock the row.
VB: ActiveCell.Offset(0, 0).Value = "Close" (And lock current row With password 123)
I wish to be able to lock an entire row after an entry has been made in a certain cell.
e.g. let's say i want to lock row 9 in its' entirety following entry of "Yes" in cell G9. If there is no entry in G9 the the entire row should be still unlocked. Likewise with any row with a corresponding entry in column G.
