i have made a simple userform in which there are 3 combo boxes. a user chooses options from these boxes and then these options are put into 3 cells in a worksheet. what i need to do is create a macro or vba code so that the contents of these 3 cells are used as the Criteria for the autofilter.
View 4 RepliesI have userform1 with commandbutton1 and listbox1. From listbox1 you make a selection and then you click commandbutton1 and it calls up another form (userform2.) On userform2 I have a label that I need to capture the value highlighted in listbox1 form userform1. How do I write this code to have label update to the listbox selection?
View 2 Replies View Related"how do you move control/focus between Userforms and a worksheet that has Control objects on it?"
Here's what I am trying to do... I have created a Workbook that is basically a database and uses several Userforms to allow users to enter data into the database. I then have various CommandButtons in the Userforms that are intended to extract information from the database (which is stored in a worksheet) and display that information on a newly created worksheet. What I want to do is have a CommandButton on the new worksheet (the new worksheet is called "Results") that when pressed will delete the Results worksheet and return control to the Userform that created it.
So, the basic steps are:
* Display the Userform with the show method
* On the _click event of a particular Command button, Add a new worksheet
* Fill the worksheet with the pertinent data
* Place a Command button on the new worksheet and call it "Close"
* Somehow transfer "focus" or control to the new worksheet so the user can move around, print, do whatever on the new worksheet and then when done press "Close"
* When "Close" is pressed, delete the newly created worksheet and then return control to the original Userform.
Excel – Forms – Combo Box, cell updating.
I have a form (the main one) which accesses a second form, which accesses a third form. All forms have a combo box, control button and a text box.
The items selected in the control boxes are entered into cells (the base cells) in the spreadsheet, via “Control Source”. The text boxes access these cells and show the selected items, which are then copied to other cells via a macro on activating the control button in the main form.
The main form stays open while other items are selected and transferred. Some of the items may not be changed (reselected) as they may be common. The problem is that after a number of items have been selected and transferred, the base cells for the second and third text boxes do not update after a selection from the combo boxes, and they continue to show a previously selected item.
The problem can be solved by closing the 2nd and 3rd forms and starting again. What can I do keep the cells updating without closing the form down?
creating 2 fields in my userform that the user can input 2 dates that would serve as the range to set the autofilter. So user inputs field1 = 11/01/2011 and then field2 = 01/31/2012.
I was previously using a month range in my userform, populating from the known months and allowing the user to select a range. Since our data is now spanning a new year, this is no longer possible as I cannot make the combobox range go from 11 - 1 (November to January)
VBA Userform - select months & Months between - Code Included
There is a macro i wrote that assigns buttons to different tasks, and it changes the name of each button to whatever task the user utilizes it for. I would like for the changes to be permanent, and i can do it if i can just keep the information in the userform. So, the most logical solution is to permanently change the names of the buttons in the userform, but this has to be accomplished by the users choice, which means i don't do it through the vbe. How do you change a button's caption permanently through macro?
View 9 Replies View RelatedI have a simple spreadsheet of an inventory. Each row on this spreadsheet represents the data related to an item. I would like for a user to be able to INPUT a serial number via a userform. Then, I would like for the program to OUTPUT some information about that particular item (a few cells that should be on the same row as the serial number).
I attached an image diagram that may better represent what I am trying to do.
I am writing a macro that will take data from a series of objects and then filter 3 columns according to their attributes.
EX.
Object 1 Example1 20140306 10
This combination of values is unique so is used to grab the value in the third column after the filter has ran.
My issue is that in the second column the actual values are 50201403069999 and they change per value although they represent the same date "20140306" so manually I can type in "20140306" and the filter will show the results however it seems the macro requests the exact which I can't provide as this the superfluous numbers change.
Is there a way to make it the auto filter search the value??
My second issue is how to extract the value sought once found. Since this cell will be different each time, what is the best way of selecting would it be C3 select then down?
Here is my current code:
Code:
Sub IMPORTPRICES()
Dim i As Integer
For Each Ativo In Sheet4.Range("A2:A6")
[Code].....
I am using the autofilter in my code.
Selection.AutoFilter field:=11, Criteria1:="reena"
Count = Sheets("ABC").AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
In the field for of the worksheet 'ABC' there are some values. I want to sort the value 'reena' and save the count in the variable Count. But sometimes the value 'reena' is not available in the field 11. Then it gives error for the above Count variable.
How to check this? How to check that the autofilter value is 'null'.
I am trying to filter data using VB. I have a bunch of data that has currency. Some of the currency contains cents such as "32.50". I would like a user to be able to enter "32" and have everthing that is 32 dollars pop up even though there is a decimil point with cents in it. In the code below I tried using different variations of the "*" I tried putting it before, after, and both and it still dosen't work. I also tried using Range("Search!D17") as a Value and a Text. My code works filtering when I put the exact amount in for the currency but for some reason I can't get it to work when I only put in a partial amount
If Range("Search!D17").Value = "" Then Goto 14 Else: Goto 13
13
Selection.AutoFilter Field:=13, Criteria1:= "*" & Range("Search!D17").Value "*", Operator:=xlAnd
14
I have a column in a sheet that has two different values in it "SB" and "SEI".
I want a macro that will allow me to switch the auto-filter between the 2 at with a shortcut button.
I have a macro that will switch the auto-filter on and off, but I was wondering if anyone had anything that will automatically switch it between two values.
I often have a table (sometimes formatted as a table, sometimes just data arranged like a table but not formatted as a table in Excel) where I'd like to create individual worksheets (eventually workbooks) based on the information in one of the columns. Take for instance the attached file. I'd like to create a macro to create worksheets (or preferably workbooks) based on data in the Region column. So workbook 1 would be something like North Region Sales 2014, and contain only the data for the North region. Workbook 2 would be something like South Region Sales 2014, and contain only the data for the South region, and so on. Sometimes I might need to create these based off the Region field, another time I might need to do it based off of the Salesperson.
Region Sales 2014.xlsx
To do this currently, I'd use the filter and unselect whichever data I want to keep and then delete all the remaining (visible) rows. When I unfilter, I'm only left with the data I want. This works, but it takes a long time when working with 50 or more "Regions" and large amounts of data.
I'm trying to use Advanced Filter to generate all the unique values within a list, only at times I want to Autofilter the data, then use the advanced filter to determine all the values of the slimmed list. For some reason, this works fine on column A, only returning the values that begin with a 1 when the column is filtered for all values beginning with 1. However, when I run the advanced filter on column 2 with column 1 filtered to all beginning with 1, all values all returned regardless of whether column 1 criteria is met. Either way, after the advanced filter, the autofilter has been removed.
View 7 Replies View RelatedI am building a table for staffing purposes, and I basically only need to controls to "validate"/approve the table.
1. The number of staff put in must equal the required amount of staff (E column)
2. The same staff cannot be put on twice for the same time period (which I have done by the OR(F7=G7,F7=H7,G7=H7) as part of the validation)
My problem is that sometimes I need 0 staff (when its closed), but when I leave it blank obviously it wont approve the table because of rule #2 above, since it thinks the blanks are the same staff member.
I have tried with some ISBLANK statements, but it wont work.
Is there a way to make the colours in a chart (pie, column etc) correspond to a value? I'm using Excel 2002, soon to start using 2003.
For example, imagine a normal pie chart showing the population of each country in North, Central and South America. But for each country, colour gradations would be used to show levels of wealth (e.g., GDP per capita) as follows:
saturated red = bottom 20% (poorest)
pink = 21-40th percentile,
white = 41-60%,
gray = 61-80%,
black = 81-100% (richest)
Is this possible? And not to get too greedy, but can you use even finer gradations -- say, with ten categories, as opposed to the five in the example above?
I have an issue with some unwanted items in a listbox that gets its data from a pivot table, however it only happens with the one pivot field and for three of its pivot items.
I tried following a suggestion from an earlier thread to re-create the pivot table, but it did not work.
I'd like to post the file, but its too large, even in zip format.
I am using a calendar control 11 in a user form. I would like to create a combobox on a user form that when you click the drop down button it opens the calender then the user can select a date which is then returned to the combobox. I beleive the way to do this is to trap the dropbuttonclick event. Tho it dosent seem to work.
Private Sub ComboBox2_DropButtonClick()
Calender.Show
ComboBox2.Value = Calender.Calendar1.Value
End Sub
I have been trying to use a multi-select listbox as the argument for a subroutine. For some reason, I keep getting a run time error, type mismatch. I'm baffled because when I define the specific listbox, it works fine, but when I attempt to pass the listbox I get an error.
Public Sub OtherDirectTotal(Expense As ComboBox, CostCat As ComboBox, _
Cost As TextBox, Years As Msforms.listbox)
Dim IDC As Double
Dim IDCt As Double
IDC = Val(UserForm2.txtIDC) / 100
If Years.Selected(0) = True Then
If Expense = "Sponsored" Then
If UserForm2.ChkODC = False Then
Select Case CostCat......................
I've built the code for a Template form for data entry of a survey. Through Control Toolbox I've created the form and used several textboxes, option buttons, checkboxes and COMBO boxes this one in turn being my trouble.
- Sheet one is called "Data" (this is the place where all inserted information is going to be stored
- Sheet two: "Variables" (here is where I keep the required values for the como boxes - inserted manually apriorely...
Question: What is the code to assing to the combo boxes in order to have the values from the sheet "Variables" of the range A1:A4 - use the comboboxq2 for referee, I'll do the others
P.S. see the code up to now:
Private Sub CommandButtonN_Click()
Dim eRow As Long
Dim ws As Worksheet
Dim inf As WorksheetFunction
Set ws = Worksheets("Data")
Set ws = Worksheets("Variables")
' Find first empty row in database survey
eRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ................
I need an autofilter which filters rows based on a list of values (+50).
The problem is that I am looking for rows INCLUDING values from the list, not for exact match.
I am not able to make excel filter values including the values from the list... I am able just to filter values matching exactly values from the list.
[Code] .....
Im sure this is a very common problem. I tried searching for it but I havent found anything that solves this for me. Here is the code Im using:
View 3 Replies View RelatedIs there any way in VBA to refer to a control in its own event procedure without referring to it by name/hard-coding?
It might be clearer to explain by a dummy code example:
[Code] ......
I'm seeking what I would need to replace Line1 with.
Within the ComboBox properties, is there anyway to control after "enter" his hit, you move to the right instead of down (similar to the edit under Tools/Options)?
View 9 Replies View RelatedI have created a userform within VBA which has a TreeView Control and a Spreadsheet control on it.
I have populated the TreeView control with data and what I want to be able to do is to drag the nodes off the TreeView control to the spreadsheet control.
I can drag onto a normal worksheet but not onto the spreadsheet control (the no drop mouse pointer keeps showing).
How do I determine which control the user is currently modifying on a multipage form (either changing, enterying or exiting the specific control). when I use "userform1.activecontrol" i get "multipage1" as the control name but I need the actual control on the specific active multipage. (also the .TABINDEX is for the multipage regardless of the on-page control) I use a generic data-field change SUBroutine so need the control name (and the TABINDEX) to provide my SELECT CASE. (so every fieldname_CHANGE calls the same SUB [with no parameters])
View 3 Replies View RelatedI can select the top cell in column "F" after filtering by multiple columns using VBA and arrays, but now want to I want to use the top cell in column "F" to search for all other equipment that uses this item.
E.g. remove filter, and reapply autofilter to column "F" based on selected cell as per below VBA
Note: Row 1 contains command buttons and row 2 Headers.
I can set the autofilter, change it, remove all using Macros. What I need to know how to do is reset a filtered column to ALL while leaving all other filters on.
View 2 Replies View RelatedI have a VBA application that seems to work fine under vista but once I start working with it in Windows 7 I notice sometimes one of my userforms do not go away when asked to hide. The code is like follows:
Application.ScreenUpdating=true
Waitingform.Hide
I have created a userform and command button within an excel spreadsheet to run the userform and then manipulate the data entered into the userform. However when I click the cancel button of the userform the macro tries to continue to manipulate the data entered, of which there is none and then comes up with an end/debug command. Is there an If and Then statement in VBA that I can use so that if the 'Cancel button' is clicked then the rest of the Macro won't run.
View 14 Replies View RelatedI designed some userforms on a worksheet for users to click and input information. However, i am wondering if it is possible to have an additional feature to the userform.
Currently the userform only have labels such as Title, Dept, Branch, Unit and the respective textboxes for user to input information. As it might be unclear to some users i would like to have an additional feature such that when user roll their mouse over the label "Branch", the user will see a box where more information is given or rather an example such as "Dept XYZ", allowing user to have a clearer idea of what to fill in. Is there any way for me to add this feature?