The code below lets me select more than one item in a column 14 dropdown and put them in the cell.
I am trying to let another column have the same properties. This time it is column 17. I tried adding another line but it doesn't work. Can anyone help with the code.
I have researched and used some code that allows the user to select mutliple items from a list. This is using the Data Validation tool with a list and some code in the relavent sheet. It all works well and as described.
However, when I lock the Worksheet, the multi selection no longer works. Selection cells are ofcourse unlocked when sheet is protected.
I have also trialed unlocking the whole row that the multi selection exists in, no effect.
My understanding of this code is at about 50%.
Is there a way of being able to lock the sheet and retain this ability of multi selection. Maybe there is some other code I could refer too?
Code:
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
[Code].....
I have a cell with data validation that I would like to look at table and be dynamic rather than a manual selection. So when I add to the list the data validation will reconise there are more options.
View 7 Replies View RelatedA list in A2 of Sheet 1 of workbook has values "Select", "Yes", "No". How can I get the functionality using VBA so that until a certain rule is met (say A1 becomes 1) selection of items in the list is disabled? (Or any selection of items in the list returns a value of select only?
View 4 Replies View RelatedHow can I select a particular data item in a data validation cell. My requirement is that I need a particular item to be displayed in data validation cell(I already have the item value with me)
I tried recording but it doesnt work.
I have two fields that do the same thing [preset selection vs custom entry], and I need to restrict one from holding any data if the other already does. One field is a drop-down menu, and the other is a decimal entry. It was easy to restrict the decimal from being entered if a drop-down selection has already been made - Data Validation prevents any entry unless the drop-down cell is blank. It's not so easy to restrict the drop-down, however, as it already uses List-type Data Validation to allow only values from a specified range.
Does anyone have any ideas for allowing the drop-down to function as normal if a specific other field or range is blank, but not allowing a selection to be made if that specific field or range already has data in it? I've attached a simplified example worksheet.
I have my data with column A being the Week ending date (a saturday date) for which the data was entered. In column B it is simply the Month that the data was entered. On another tab...the user can select weekly or monthly. What I would like is a validation drop-down that corresponds to the selection.
IE - If weekly is chosen, the validation will allow the user to select a week that is found in Column A in the data. If monthly is chosen, the validation will show the months from Column B instead. Is it possible to have one validation dropdown, that the validation LIST can be conditionally select based on input in another cell? (A1 being Weekly or Monthly - and B1 being where the validation dropdown would be)
I have a workbook with various "in cell" validation selection boxes.
All worked well, but now the width of de dropdownbox is very big, stretching over several cells. The cell width is 70 pixels, but the dropdown box is 500 width.
I tried new validation, but no matter where I place a validation of what list I use in validation the width of the dropdown is always over 500 pixels? How to change that?
I have a large set of data that I need to use for data validation in drop down lists. The data is arranged in 4 columns and entries within each column may repeat numerous times. The four columns are as this..
Category Subcategory 1 Subcategory2 Subcategory3
The first data validation will come from the first column. Once this is chosen the second drop down should populate with the subcategory that correspond to that category stored in column 2. once that is chosen the thrid data validation should populate with the corresponding subcategory from column three etc.. This is a small sample of the data in the columns.
I have been searching through the site trying to find a solution to my issue, but I just can't seen to fixt it. Here is the scenario. I am working on a spreadsheet that will read some data, use that data to update the main chart and fill in the information in a table next to that chart. I recoreded 22 macros, total. Each macro corresponds to the especific type of chart that I need/want.
I want to be able to click on a drop down menu above the chart which will have each of the 22 options. Once the user clicks on the desired option, the specific macro will run and do that it should do. When I run the individual macro (without the VB that I wrote and will mention below), the system does what it should do. Everything looks dandy. But then I added this VB code to the sheet where the chart is:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Range("A12").Value
Case "1"
Call a
Case "2"
Call b
Case "3"
Call cc
Case "4"
Call d
Case "5"
Call e................
Basically I have a form where the list can expand and contract so there will always be varying row lengths.
What I would like to see is a drop down list that shows a) the actual populated field and b) a description of the field when the drop down list is present.
I believe this can be done with a combo box however that would be a lot of work.
I am trying to find something that would allow me to have a spreadsheet clear the contents of a certain cell based on the selection made from a data validation list which resides on the same row. This can best be described with an example.
The user selects anything but " " or "none" from the validation list, which for this example resides in C5, would trigger code to clear the contents, if any, in AA5. If " " or "none" is chosen the value remains.
This capability should be available for each row through 100.
I'm using Excel 2007. I would like to seek some advise on how i may update cells after selecting a month from a drop down list.
i have created a simplified version of what i intend to have. One the 1st tab 'Cash Budget 2009', i have filled up a table with numbers, sorted by months. On the 2nd tab 'Dec09', i would like to create an expense table, to be able to be selected by month. So i have created a drop down list based on the months that are created in the 1st tab.
Upon selection of the month from the drop down list, i would like the cells to display (fetch) the data from the 1st worksheet. It would also be helpful if the name of the 2nd tab can be updated to read as the month that is being selected.
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.
View 6 Replies View RelatedI am trying to create a fairly simple spreadsheet with about 8 columns and about 400 rows. One of the columns features a drop-down list with about 8 or 9 different options. Dependant on which option is selected, i would like the entire row to change colour with that option.
For example:
FAILED - whole row changes red
SUCCESSFUL - row has no fill
Tested - row changes to orange
etc.
Is this possible within Excel 2003?
I want to load the actual data exist in the rows by selection value from the data validation list.
E.g I have two worksheet in one excel file. One has a data activities of persons with their name like two columns i have in which one exist the name of person and second exist the activities which they perform.
On the second sheet, i made a data validation list of all the person names
Now my requirement is, when i select a person name from the list, load all the data from the 1st sheet to second sheet. Is this possible without VB code, because I want to share it on the Google sheet with my boss, where VB sheet is not supposed to work.
I have a spreadsheet setup like a timesheet: Employee Name, Classification, Rate, etc.
I setup the Employee Name field as a drop-down. Since there are numerous employees with different rates, I want to just select a name so that the other fields fill-in with the appropriate rates.
I am putting together an order expediting sheet.
I get the code to refer to single cells, but cannot get it to refer to multiple cells in the same column.
The code i am using is:
Private Sub CommandButton1_Click()
If Sheets("Sheet1").Range("E6") > Sheets("Sheet1").Range("B2") + 3 Then Sheets("Sheet1").Range("F6") = "YES"
If Sheets("Sheet1").Range("G6") = "YES" Then Sheets("Sheet1").Range("F6").Clear
If Sheets("Sheet1").Range("F6") = "YES" Then Range("F6").Interior.Color = vbRed
End Sub
I need to have the ranges E6, F6, G6 to refer to E6 - E10000, F6 - F10000, G6 - G10000
In one of the worksheet. I have list of questions in column A.
In column B I want to have the following 3 checkbox
1> YES
2> NO
3> Not sure.
Based on the question, user selectts one of the check box.
User should not be able to select multiple checkbox. i.e. User can select only 1 out 3 possible options.
I want to copy a selection of cells and paste them into a row on another
sheet.
An example would be to copy:
Sheets("Sheet1").Range("T5,C7:T9,T13,C15:T17,T19,C21:T23")
and put it into A4:F4 on Sheet2
I know I can do it one cell at a time but I was hoping that I could speed
things up and do it all in one go as the macro will be quite long.
I am trying to create a simple order entry worksheet. In cell M1 I need a dropdown box that lets the user select which options go on the product. You can have multiple options. The list of options is contained in column A of the Options worksheet. Depending on which options are selected, I want to put a 1 in column B of the Options worksheet. how to do this or have a sample workbook that I can look at.
View 1 Replies View RelatedThere are multiple training sessions that will be rolling out in Q1 13 and my manager needs a way to track which empolyees have been to which training sessions. Easy enough. He wants something extremely clean looking, but obviously efficient. What he described to me (his vision) is to have the training sessions listed into columns with drop down under each that contain every employee's name. So when a training session is held, he can easily click the drop down beneath the session title and select multiple persons that were in attendance. Right now I just have all the employees listed in column A and all the training sessions listed across the top and anticipated just putting a "x" in the box for the session that each person attends. This will accomplish exactly what he's looking for, but if what he's describing is remotely possible I would like to do that.
View 2 Replies View RelatedI have a userform where I want the user to be able to select multiple options, but am having a bit of a problem finding the best way to do this. If I use a combobox, I don't appear to be able to allow multiple selections, but if I use a listbox I don't appear to be able to implement a dropdown facility. I would prefer to have a single line sized box on my form, but doing this with a listbox would probably be confusing for the user as it is not very easy to see what has been selected.
View 11 Replies View RelatedI want to do is randomly pull from a field of data, MINUS any cells that do not meet the criteria in other columns. So if column A is my target column for the random selection (containing 10 unique entries), and column B has 10 repeating colors, and column C has 10 repeating animals, what can I use to perhaps create this effect: A random pull from column A, but only from rows in which column B says red and column C says cat? (The pull being 1, 4, 7, or 9 in the below example, of course.)
Data (this example is also attached in an excel doc):
One Red Cat
Two White Dog
Three Blue Horse
Four Red Cat
Five White Cat
Six Blue Cat
Seven Red Cat
Eight Red Dog
Nine Red Cat
Ten Red Horse
I have tried variations on IF and INDEX to no avail. Perhaps something with SUMPRODUCT?
I want to filter a list. Within the filter I want to copy a selection of cells and hard copy the formula in those cells (copy paste special values). It is possible to selected the cell and copy them but one cannot paste on a filtered range. The error message I get is "The command cannot be used on multiple selection".
View 2 Replies View RelatedI have an excel sheet where I can enter a text into A4, when this is valid a checkbox1 will appear if there is no value then the checkbox is invisible.
I have used a macro 'worksheet selection change' to do this but unfortunately I can only use this once in a sheet. I need to do this for upto 10 checkboxs that corrospond sequentially with text starting at A4 for checkbox1, A5 for checkbox2 etc....
The routine I have used for one check box is below. Can this be changed for multiple checkboxes?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A4") = isblank Then
CheckBox1.Visible = False
Else
CheckBox1.Visible = True
End If
End Sub
below is an excel file with a UserForm.
The goal here is to select the worksheets in one list box and transfer it to another. Multiple Selection is allowed. I am having 2 question i am struggling with.
Question 1 -
When passing the sheet names from List Box 1 to List Box 2 (using Add button) i would like the selected items of List Box 1 to be deleted after transfer. I thought i could access this using the command below but it does not work properly -> error message.
UF_PI_LINK_1.LB_SheetList.RemoveItem UF_PI_LINK_1.LB_SheetList.List(i)
Question 2 -
When the user is done the ListBox 2 need to be stored. When I save then close my workbook and then reopen it, i would like to be able to access the selection that the user has made before closing the worksheet. How can I store the content of ListBox 2 to achieve such a result. I guess i could use a use a hidden Worksheet where i could store the selection into some cells but i would like to know if there is a pure VB solution.
So I have a quote format with various different products:
B1 to B10 Contains a vlookup with stock codes from another page. C1 TO C20 Contains drop boxes with various different stock items. Some of the stock items are available in singular form and others are in kits. I.e C1 could contain whats in C2. I want to be able to select C1 and have the sheet automatically pull up c2. But I dont want to loose any data that is in c2, in other words I still want to be able to manually select C2.
I have a spreadsheet where I've disallowed selection of multiple cells using the code in the 2nd box below, but is there a way to override this protection to allow selection of a named range? My reason for doing so is to open this workbook as read only from code in another workbook and copy the entire table. My code for doing that works ok (I frequently use it in other workbooks) but, in this case, my need to disallow selection of multiple cells in this particular worksheet is tripping me up.
This is the code I'm using to try and pull the data into another workbook:
Code:
Workbooks.Open Filename:=ThisWorkbook.Path & "RSL-Jobs-Register.xls", ReadOnly:=True
Application.GoTo Reference:="JobsTable"
Selection.Copy
and this is the code I have in RSL-Jobs-Register.xls which is preventing me from selecting the (dynamic) named range called "JobsTable".
Code:
''' I'm thinking I could have something here like (in plain English) "If selected range "JobsTable" Then"
''' (apply the code below) but I'm not sure of the code
If Selection.Cells.Count > 1 Then
MsgBox "Sorry, operations on multiple selections aren't allowed here", vbCritical
ActiveCell.Select
Exit Sub
End If
The formula for the dynamic named range called "JobsTable":
=OFFSET(Jobs!$A$2,1,0,COUNTA(Jobs!$A:$A)-2,11)
I need to populate a worksheet's rows with values from 2 comboboxes and a listbox which can have multiply selected items. After finding the row's last free cell, I have the listbox values properly populating correct column. However, I not sure how to get the corresponding combobox values assigned to the appropriate columns.
View 2 Replies View Related