Disable Control Based On Selection
I am working on a form in VB and have a combobox and three monthviews. My question is when a user selects a specific item from the combobox (ex. terminal illness), I want one my monthviews to disable.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Disable Menu Control Edit Mode
I would like my custom menus to act like builtin menus - specifically I would like them to be disabled (grayed out or even entirely removed) when the application is in Cell Edit Mode (I cant believe I am the first to ask about this but I cant find the answer elsewhere). It sure seems confusing to offer users access to a seemingly active command when it does not run (in edit mode I mean). I send huge lumps of Karma to any willing to share ideas or solutions.
View Replies!
View Related
Disable Or Stop ActiveX Control Events Firing
I've several check boxes on a worksheet. Some of the check boxes control the values of other through the _click event. However, when I try to change the value the event attached to the control in questions fires. application.enableevents = False would prevent events being fire from within the macro but this only seems to work when I step through the code. I understand that I could set a global variable to do this and will probably go down this route anyway - but I am interested to know how/when I should be using the EnableEvent method (as I am sure this should be the way that I use it)
View Replies!
View Related
Disable Selection Of A Validation List
A 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 Replies!
View Related
Copy & Paste Selection Based On Combo Box Selection
I have attached a portion of an excel file I am using at the moment. What I require is a piece of VBA code which will allow me to copy selected data based on a combo box selection. What needs to happen is this :- If the selection in the "Index" worksheet combo box related to "Month" in cell G19 is for instance "December", I would like the macro to compare this cell value to the cell values in cells C96, C124, C152, C180, C209, C236, C263 and C290 in the "Tech Services" worksheet and where the values match.......copy the commentary (Range C126:Z147 in December's case) to the range C34:Z55.
View Replies!
View Related
Command Button Control Of Sheet Selection
I would like to know how to configure buttons: In tab Sheet1 there is Commandbuttons named "Sheet2" & "Sheet3". If i press "Sheet2" then i will be directed to sheet2 tab, same as "Sheet3". In tab Sheet2 & Sheet3 there is commandbutton "Sheet1", if i press the button then i will be directed to default tab which is sheet1.
View Replies!
View Related
Restrict Drop-Down Selection Based On Selection From Another
I want to have a drop down list in a cell so that the value in the cell can be only selected from two columns of data. Additionally once the data from the first column has been selected I want to be able to limit the inputs the user can select from in the second column. e.g. In cell C115 I want to have the value BDS05. I want to be able to select the value BDS from a drop down list of values and once that value has been selected I want to be able select 05 from a list of values from 01 to 14 If I select BCS as the first value then I want the second set of values to be limited to 01 to 02 etc. I have read about combo boxes and list boxes and I'm a bit confused about the best way to achieve this (or even if I can).
View Replies!
View Related
Enable Disable Form Controls Based On Criteria
I am creating a form that performs several different functions. I would like to force a sequence of these functions by enabling/disabling the controls based on a variable. For example, a form as two buttons Button1 and Button2. By default, Button1 is ENABLED and Button2 is DISABLED. When Button1 is clicked, a macro is run, where an Enable_Button2variable is initialized to FALSE. The last line of the macro sets the Enable_Button2 variable to TRUE. Then, because Enable_Button2 is now TRUE, Button2 then becomes ENABLED. I am trying to apply this strategy to a ComboBox, ListBox and CommandButtons. I'm not sure what event to trigger on to poll the Enable_Button2 to enable/disable the control.
View Replies!
View Related
Enable & Disable Worksheet Controls Based On ComboBox Value
I'm currently building an Excel database. This database has a few comboboxes and checkboxes that are tied to each other. If I select the combobox option of "Years", the "Quarters" combobox must be greyed out. In addition depending on which option is chosen, the relevant check boxes must be enabeled or disabled. I've written some code which runs fine, until I try and activate another macro button or if I add and rename a sheet(which is not linked to anything). What should I do? Here is a sample of my code. I have a feeling it's really obviouse but I cant see it.. Private Sub ComboBox2_Change() If Sheets("InputSheet").ComboBox2 = "Years" Then Sheets("InputSheet").ComboBox3.Value = "" Sheets("InputSheet").ComboBox3.Enabled = False Sheets("InputSheet").CheckBox6.Enabled = True Sheets("InputSheet").CheckBox7.Enabled = True Sheets("InputSheet").CheckBox8.Enabled = True Sheets("InputSheet").CheckBox9.Enabled = True Sheets("InputSheet").CheckBox10.Enabled = True Sheets("InputSheet").CheckBox11.Enabled = True Sheets("InputSheet").CheckBox12.Enabled = True Sheets("InputSheet").CheckBox13.Enabled = True Sheets("InputSheet").CheckBox14.Enabled = True End If...................
View Replies!
View Related
Value For Variable Based Off Control Checkbox.
I have several checkboxs in an excel sheet, that if one is checked i would like it to make a change to a variable in a macro that will run when saving a file. for an example: if checked: Checkbox1 = NCE1 Checkbox2 = NCE2 Checkbox3 = NCE3 Checkbox4 = NCE4 Checkbox5 = NCE5 I have a bit of code that is like this.... ActiveWorkbook.SaveAs ("\marketing2PartageNonconforms" & sF1 & " - CO" & sF2 & " - FC" & sF3 & ".xls") I would like sF1 to change depending on which checkbox is checked. so if checkbox1 is checked, than sF1 = NCE1 .
View Replies!
View Related
Text Based Treeview Control
i found this quick online tutorial [url] on treeviews. i was trying to modify the code to make it a simple tree contaning just text like attached image, and to have a command buttton to add, and delete from the tree.
View Replies!
View Related
Vba Cell Control Based On The Cells Contents
Is it possible to place formulas in vba to control cells based on the cells contents. A2 will be the the price of an item excluding vat and b2 will be the price of an item including vat What i want to achieve is if I put a figure into a2 b2 then multiplies a2 by 1.15 to reflect the price including vat. If this is achievable I then want to reverse the proceedure so if I put the price including VAT into b2 a2 divides b2 by 1.15 to reflect the price exc. VAT.# if all of the above is clear and possible I would like to know if it is easily possible to copy the workings for a3, 4 ,5 ,6 etc and b3, 4, 5, 6 etc.
View Replies!
View Related
Select Worksheet Based On ComboBox Control Choice
i have a user form with 4 combo boxes and one text box. what i've tried to do is make it so that if the month combo box reads january, then all of the info is placed in a worksheet called january. at the moment it is putting all the info inputted into one sheet. if some one could take a peek at the code below. If cbomonth.Value = January Then Sheets("January").Select Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) Or IsEmpty(ActiveCell.Offset(0, 1)) Or IsEmpty.....................
View Replies!
View Related
Set Control Visibility Based On Tag Property
I have a userform which uses the tag property of the frame to determine whether a frame is visible (and hence the controls that reside within the frame). A frame contains three combo boxes, and six text boxes. The tag property of the frame matches the number of frames that are visible on the form, so that if the user selects five frames, frames 1 through five become visible and for all other frames visible = false. A frame contains all the data for a single entry. There can be up to fifty frames/ entries that are visible on the form depending on the user selection. I want to use the visibility property to do two things: first the combo boxes are filled from an array after the user selects the number of frames (or entries). I only want to fill the comboboxes where the frame is visible. The second thing is that I have a function which uses the data from the text boxes and combo boxes as required arguments. Since these are required arguments, I will get a data mismatch error if I try to call it and the controls are empty. Therefore, I only want to call the function if the frame which houses the controls is visible. Public Sub Visible1(Entry As String) Dim ctrl As Control For Each ctrl In UserForm2.Controls If TypeName(ctrl) = "Frame" And ctrl.Tag <= Entry Then With ctrl .Visible = True End With Else If TypeName(ctrl) = "Frame" And ctrl.Tag > Entry Then With ctrl .Visible = False.............
View Replies!
View Related
Populating Combobox Based On Selection Of Another
I have a problem involving two combo boxes in a user form. I need to populate different “lists” to combo 2, based on a selection in combo 1. I browsed through the forum but could not find any solution that made sense to me. Here are the specifics: Combo 1 (called Cbo_Act) is populated with a list based on a named range in one of the sheets. The range is named “activity” and holds 2 records (“Income” & “Expense”). This list is loaded as part of the form initialization. If the user select “Income”, I need combo 2 (called Cbo_Act_Type) to show a list of various income types (derived from a dynamic named range called “Income_type”) and if the user selects “Expense”, I need the same combo box (Cbo_Act_Type) to show a different list, specifically – a list of various expense types (derived from a dynamic named range called “Expense_type”). I know that this should be with a Cbo_Act_Change () routine, but for the life of me, I can’t figure out how to do it.
View Replies!
View Related
Hide Columns Based On Selection
I have a worksheet that lists the months of the year on row 3 from columns C through N. What I am trying to do is to be able to select the month I want from a drop down list or combobox and then hide the remaining columns based on the selection. For example if JAN is selected the months FEB through to DEC would be hidden.
View Replies!
View Related
Open Files Based On Selection
I have multiple files in one directory. I have a master sheet that gives me basic information about all those files. In column K it list's all the file names and file paths. I would like to be able to use the auto filter to sort these files. Once the files are sorted they are opened and the relevant information (the CHRD page) is then copied into the master (Query.xls) the first copied page is renamed to sheet1 and second to sheet2 etc. Right now i have to manually run the macro's depending on which line the files are listed. I have also had to create separate macro's for each line.
View Replies!
View Related
Populate ListBox Based On Selection In Another
if is possible to make some kind of ComboBox without filters, I mean that let you choose different items at the same time. I do not know if some of you have seen some "ComboBox" that has all the options in one side and next to this window it has a blank window that when you double click the word that was in the other wondow passes to the other window telling you that you have chossen this item.
View Replies!
View Related
Random Selection Based On Criteria
I am working on a challenging project. I have a list of names, job titles, and departments. Is it possible to select a random name who matches a specified job title or department? I'd prefer not to sort/filter/delete the original data range because the sheet will be used multiple times during the day by multiple users and with different criteria each time.
View Replies!
View Related
Format Selection Based On Criteria
I'm trying to write code that will allow Options>View - Zero Values.", "style="background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;"");' onmouseout='GAL_hidepopup();'>formatting.htm" target="_blank">conditional formatting using 5 conditions. I have the following: Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:G10")) Is Nothing Then Select Case Target Case Is <= -9 icolor = 4 Case -8.9999999 To 8.999999999 icolor = 6 Case Is >= 9 icolor = 3 End Select Target.Interior.ColorIndex = icolor End If If Not Intersect(Target, Range("A1:G10")) Is Nothing Then............... The problem is I need to apply this formatting to a large chunk of cells that are already in a worksheet - or to be able to copy the cells into a new worksheet to be formatted alll in one go. Obviously I need to use something other than worksheet change in the code, but I don't know what....
View Replies!
View Related
Populate Drop Down Based On Selection Of Another
I have a dashboard that uses three pivots based off of a single data source. Each of the pivots is designed to work off of their own drop down list. the first drop down list allows the user to select a lease date. The associated pivot will update with all servers assocated with that lease date. the second drop down list allows the user to select a server. This list is limited by the lease date selected in the first drop down. The associated pivot will update with the partition information for the server selected in the drop down. the third drop down list allows the user to select a partition. This list is limited by the server selected in the second drop down. The associated pivot will update with the detailed partition data for the partition selected in the drop down. All of the above works, the issue is that when a new lease date is selected in the first drop down list, the current entries for the second and third drop down list are still reflecting the old lease date information. When you click the drop down the correct data is there, it is just not automatically populated. Is there a way that when the user selects a new lease date in drop down one, the first server in the new list is automatically populated in the second drop down as well as the first partition in the third drop down?
View Replies!
View Related
Fill ComboBox Based On Selection In Another
I want to dynamically list items in combobox placed in excel sheet. I have a list of 10 values in a column in excel sheet. I have placed two comboboxes in the same sheet. Now depending on the first combobox selected value i want to list either all or only few values from the available 10 values.
View Replies!
View Related
Updating Cells Based On List Selection
I am working on a spreadsheet that is essentially a question and answer based document .... I need to set up the macros so that if a certain response is chosen from a list, it will automatically update the cell / row next to it with a pre-determined response .... The way the spreadsheet is set up is that the first column has the question, the second column has the answer (yes, no or N/A - from data validation - as a list) and the third column is blank - but is titled "comment/action" ..... For example: if the question is "have you locked the car" and the answer column selects "NO" then I want the "comments/action" to come back with "you need to lock the car", and if the answer is "YES" then I want the comment/actions column to remain blank ...... The same with N/A ...... I will have around 100 questions in the document ...... I know I need to set up the responses on a separate worksheet - i just dont know what the macro's are to identify and place the appropriate response from the worksheet to the relevant comment/action column .....
View Replies!
View Related
Run Macro Based On ComboBox Selection
can i use a combo box with a dropbuttonclick to run the macro only when the arrow is selected . The problem I am having is the macro will run on the selection of the arrow which is whatt I want but it also run when I make a selection it the combo box
View Replies!
View Related
Hide Rows Based On Combobox Selection
I know there are a lot of hide row threads... i've searched for ages... but (unfortunately) i can't a solution that addresses my problem and I'm stuck. Please help! I have a data between rows 10 and 1310 and I would like to use a combo box option to select and view certain rows of data only. I don't want to use auto filter because i have a separate macro using an auto filter on the same data - they won't play together. So in short - combo box will contain values from column A. When value is selected from combo box hide all non matching rows based on column A values. So i have added a combo box to sheet1 and populated it with the following workbook code... Sub Workbook_Open() With Sheets("Sheet1").ComboBox1 .AddItem "Select Product Type" .AddItem "Apples" .AddItem "Mangoes" .AddItem "Oranges" .AddItem "Clothes pegs"........
View Replies!
View Related
Populating Data From One Sheet To Another Based On Selection
I need help to create a formula that would grab data from one sheet and populate another sheet based on the employee that is selected from a drop down list. Attached is a draft of what I am looking to do. Basically this would allow us to enter employee variances from several employees on one sheet and get a detailed break down of their history on another sheet.
View Replies!
View Related
Clear Contents Based On A Selection In Another Cell.
If I have a pick list, or drop-down list, in cell B21 and one of the options in that list is "Clear", how can I get the contents of cell C21 to be cleared when I select the "Clear" option in B21? Constraint... Don't want to use VBA. I am thinking of hiding a formula somewhere other than in C21 that evaluates B21 and does an If Then kind of deal to clear C21.
View Replies!
View Related
Call List Of Values Based On Selection
I have 2 Fields (Columns) and I have implemented Validation Lists in both these 2 columns. I have around 10 values in Column A which are available in the List and have around 50 values in Column B in the List. In other words, one of the five values or items can be selected for a single and unique value in Column A. is it possible to call one of these 5 values in Column B depending upon the selection of the value in Column A ? Both the columns have validation lists.
View Replies!
View Related
Delete Entire Row Based On Selection
I have a worksheet where I have restricted the users from scrolling around beyond a certain range. Because of that the user is prevented from selecting an entire row. There are some cases where the user should be able to delete entire row or range of rows. How can I allow them to select say cells "C17:C35" [they would just hoghlight the range] and hit ctrl+d; which would trigger a macro and the macro in turn would delete all the rows in that selection [rows 17 through 35]? I know some basic codes for deleting entire rows, but I don't know how to read first and last row of the selection under Target property.
View Replies!
View Related
Delete Row Based On Range Selection
Here is my ThisWorkbook.sheets("sheet1").Range("M").Select For Each cell In Selection If cell.Value = ThisWorkbook.Sheets("Sheet2").Range("A").Value Then cell.EntireRow.Delete End If Next cell Range("a1").Select End Sub I want to remove all rows in sheet 1 that contain any value found in Sheet2 A I using XL 2003.
View Replies!
View Related
Update Cells Based On A Dropdown Selection
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.
View Replies!
View Related
Hide Rows Based On Drop-Down Selection
I have a shared worksheet that has many rows of data... but only certain rows are relevant to each specific users. I would like each user to be able to select their name from a drop-down list in order to have all rows that are not important to them be hidden. I can put the name of the user in column 'A' in each row they need to see, but how could I make all rows that do not contain their name in column 'A' hide? In other words, if 'Bill' is selected from a drop-down list, only rows that contain 'Bill' in column 'A' would be seen.
View Replies!
View Related
Fill Textbox Based On ComboBox Selection
There is a small command button in cell A11 that brings up the userform. I have a multi-tab user form the userform contains a number of combo boxes with an associated textbox right of each. I would like to populate the textbox based on the value selected in the combo box. For example when Ice Foot is selected (Type of Fast Ice) populate the textbox (txtFastIceEncoded) with the value of 6. These values are located on 'decode' sheet. I then want to take the four values and place them in the textbox 'txt_Tw_Tw_Et_DE_ai_group'. Ultimately I want the values to end up back in the worksheet 'Synoptic Ice Obs'. Perhaps there is a better way to do this than I am trying. I have poured through countless threads in this forum and have tried a number of these. I however always seems to run into some problem that I do not understand fully. I have included a copy of the workbook.
View Replies!
View Related
Populate Listbox Based On Combobox Selection
My table contains 6 columns, one of which is 'season'. I have a form containing a combobox and a listbox. The combobox has 4 options (spring summer autumn and winter). I want the listbox to be populated with all rows containing the selected season. For example, if i select "spring", the listbox will show all rows in the table that have spring in the season column. I realise this is a very remedial question but i am very new to VBA and programming in general!
View Replies!
View Related
Copying Rows Based On Range Selection
I would like to copy selected rows in a range D19:I49 to another sheet, by clicking a button to bring up a User Form containing a List Box, seven Option Buttons (one for each sheet), an OK and Cancel Button. When the User Form is displayed, the required rows are selected in the List Box, then the required sheet is selected via an Option Button. Upon clicking the OK button it pastes (only the values) to the selected sheet, but to the next empty row (if there is already data entered previously) in the same range as above (D19:I49). All seven sheets are identical and are named Monday, Tuesday, etc. to Sunday. If the option button selected is the same as the active sheet a message box could be displayed to ask the user to select another option button and upon clicking the OK button on the message box return the user to the User Form once again so that the correct option button may be selected.
View Replies!
View Related
Change Cells Format Based On Selection
I have created a named range that includes raw data layed out in 4 columns (Finish Name, Fill Color, Pattern, Font Color). On a separate sheet, when one of the finish names is selected in a particular cell, I want the cells fill, pattern, and font color to change according to the raw data. Below is the vba code I am trying to get to work. However, it is not working. It does not break, but does not achieve the desired result. It looks like the variables are getting set correctly, but still not changing the cell when they are referenced. I am only testing it on the first finish color, as seen in the code. All of the rest are static values for the cell, but I want the values to be dynamic. Sub SetFinishSeparatorColor() ActiveCell = frmFinishSeparator.cboFinish.Value Set rngTarget = ActiveCell With Sheets("Pick List Do Not Tamper").Range("Finish_Cell_Color") lngFinishCellColor = . Find(What:=rngTarget, After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1)..............
View Replies!
View Related
Populate Cell Based On A Dropdown Selection Of Another
I found many examples on this forum of how one can pull in data from closed workbooks, or copy it and have it stored in the active sheet. So far so good, but I need a solution which would do the opposite - I need the macro to take a range of data form the active sheet store it in a closed workbook and save any changes in the target workbook without opening it.
View Replies!
View Related
Enable Different Macros Based On Combo Box Selection
I have a spreadsheet with 2 combo boxes - Combobox 1 enables a user to specify if they want to look at the data on the sheet by week or month - it has just 2 options - "select by week" or "select by month" The list in combobox 2 changes based on the selection in combobox1 - so if you select "select by week" , a date list by week shows up. Combobox 2 is supposed to run a macro which updates some backend pivot tables. there are 2 types of backend pivottable - weekly tables and monthly tables. What i am not sure about is how to get the selections in combobox 2 to run either the weekly macro or the monthly macro based on if combobox1 is set to week or month ...?
View Replies!
View Related
Updating Cells Based On Drop Down List Selection
I have created a pricing sensitivity for a list of products. In cell C3, users can select a product (i.e. Apple, Elmo, or Bowl) and based on this selection, a range of Prices and Units are then updated and the Revenue for each price point calculated. A few lines beneath this, I have a little summary table that lists all the products. I'd like for users to be able to enter in the optimal price point for each item and then have the Units and Revenue for that selected price point for that specific product automatically update. For example, if I select Apple from my drop down list my price choices are $5, $10, $15; units are 100, 85, 70; and revenue are $500, $850, and $1,050 respectively. On the summary table, I would like units 85, revenue $850 to update automatically if I select/input a $10 price. This would happen for all the products so at the end I want to be able to calculate a blended revenue mix given my pricing changes.
View Replies!
View Related
Random Option Selection Based On Variable Input
I need an action to allow for the random selection of a predetermined series of number codes which represent a given letter. It is probably easier to look at the attached sheet and the previous discussion to see what I mean. Previous discussion: http://www.excelforum.com/excel-work...-of-cells.html
View Replies!
View Related
Changing Values Based On Drop Down Menu Selection
Attached is the file i am working with. The table spanning from A:AA5:16 contains hours worked per day of the week with respect to the Project chosen from the drop down menus in A7:16. Cells in the range of F,K,O,R,U,X,AA7:16 total those hours worked on a daily basis. The table below should sum the total hours worked per day according to the Project selected in A19 from the drop down menu, with respect to the Project(s) chosen in cell range A7:16. For example, if i am looking for total hours worked Monday on Project RSB i select "RSB" from the drop down menu in cell A19, i want cell F18:19 to be the sum of cells A7 and A9, with respect to the Project selected from cells A7:16. If I select "FRG" from the drop down in cell A19, i want cell F18:19 to reflect the sum of cells F8 and F10.
View Replies!
View Related
|
TRACKER
