ComboBox Control Source Update With Multiple UserForms
Oct 12, 2006
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?
View 9 Replies
ADVERTISEMENT
Dec 16, 2013
The spreadsheet is comprised of 4 sales data sheets, one for each store. Each of these sales data sheets runs several pivot tables (One for dept sales, one for waste, one for sales on Selected UPCs etc) and I used to have 1 copy of each pivot table for each store (ie 24 pivot tables). Now I have found a way to have only 1 copy of each PivotTable which changes based on which store is selected using the following code:
Code:
Sheet13.PivotTables ("Top10LW").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
.List(.Value)
This works great however I was repeating it for every pivot table I had in the spreadsheet, causing messy code and file corruptions.
Is there any code which I can use for updating all pivot tables, using as minimal code as possible, all in one hit?
View 3 Replies
View Related
Oct 22, 2013
I want to display mutliple columns in a form control combobox. Tried activex and the easy part was the multiple columns. I couldn't get the activex combobox to update properly when a cell value changed.
The form control combobox updates properly but it is only displaying the first column of my named range.
View 6 Replies
View Related
Feb 21, 2008
in A1 there will be a box with LinkedCell set to A1 and ListFillRange set to B1:B5. I now want a similar box in A2, with A2 as LinkedCell. However, when I copy paste the box in A1, it still refers to A1 and B1:B5 (the last part is ok). I can easily change A1 to A2 by just changing it in the properties, but the thing is that I also need these boxes in A3:A100. Someone else helped me out by writing some code, but this is not working correctly yet, probably because I'm doing something wrong.
Also, the ListFillRange might expand in the future. Therefor I would like to know if its possible to select the boxes in A1:A100 and change the ListFillRange to B1:B6 for all at once, or even have a piece of code attached to a command button that recognizes the expanded range and updates it.
I couldn't attach the file because it was to big, therefor I uploaded it with yousendit:
[url]
So in short: I would like all the comboboxes in column E to be linked to the cell underneath and have and option to adjust the listfillrange. The same for column F, but for this one the listfillrange needs to be O8:O11.
View 9 Replies
View Related
Aug 10, 2006
"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.
View 3 Replies
View Related
Sep 1, 2006
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 Replies
View Related
Jan 16, 2008
I 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
Nov 24, 2011
I am trying to use code here to facilitate "cascading" comboboxes. However, my comboboxes are in a worksheet, not a userform and I am unsure which bits of the syntax are incorrect.
Currently I am stuck at
" . . . . .
For I = nr + 1 To N
Controls("ComboBox" & I).Clear
. . . . "
The error message tells me that "Sub or Function not defined."
I think (??) the Controls function relates to User Forms, not activex and worksheets, but I don't know what to replace it with or how to alter the syntax.
View 2 Replies
View Related
Jan 16, 2012
I have created a userform where I have a combo box and an options box. I want the combo box to reference a specific list of values. And if the check box is selected, I want the same combo box to reference a different list of values. This is the present syntax I am using. For some reason, The value of the check box does not alter the list in my macro.
Private Sub Userform_Initialize()
If chkHeader.Value = True Then
cmbSort1.Clear
With cmbSort1
.AddItem Range("A1")
.AddItem Range("B1")
.AddItem Range("C1")
[code].....
View 9 Replies
View Related
Oct 26, 2003
I am creating a Userform and one of the elements on it is a combobox where the user has to select their name. On the same Userform, I want to have a textbox (or listbox or another combo box if necessary) which will show the users default cost centre based on the initial combobox selection. I've tried doing this various ways, but I can't get the textbox to update when the combobox selection changes.
View 7 Replies
View Related
Aug 31, 2007
I am trying to set a controlsource property so that when a cell on a worksheeet is changed, the label will also change. However, when I try to set the controlsource I get an error that says :
Could not set the ControlSource property. Invalid property value.
I've tried it with a label in the code,
lblUser.ControlSource = Worksheets("User List").Range("C1")
and I've tried it in the properties window of a textbox, and I cannot get either to work. I've also unprotected the sheet first, thinking that might have something to do with it.
View 5 Replies
View Related
Jul 14, 2006
I have a workbork for employee time keeping. I have designed an Input Box that has 15 text boxes (7 diff hour types, 2 weeks, one total box). I have everything working properly except I want to make the control source relative. When the user clicks on a name of an employee (A column), then clicks the macro button, the Input Box appears. I need the text boxes to be linked to the cells E:S on the same row as the active cell. I've tried typing in ActiveCell.Offset(0,4) and variants of it, but all are rejected. How can I link the text boxes using active cell and offset?
View 8 Replies
View Related
Dec 6, 2006
I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem
...
.Fields.Item(1).value = activecell 'activecell value = "Joseph"
If Not isempty(activecell.offset(0,1)) Then
.Fields.Item(2).value = activecell.offset(0,1).value
Else
.Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works
End If
...
End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty
' or
.Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
View 2 Replies
View Related
Oct 26, 2008
why this is causing an error upon opening the file. This workbook has a userform with a listbox (single list, single selection), and when I have the ControlSource property set it causes an error upon opening the userform (which opens when the file is opened), whereas if I do not have the ControlSource property set to anything, it seems to work fine.
Interestingly, if I set the file up to not open the userform with the file, and instead have the userform open when a worksheet control such as a command button is clicked, it doesn't seem to matter if the ControlSource property is set or not. The workbook in question is attached.
View 14 Replies
View Related
Apr 28, 2008
Below is my code. I need the row source for my combobox to be dynamic. I created a named formula and referenced the formula in my code. It doesn't work.
This is the formula "listname"
=OFFSET(MyList!$C$2,0,0,COUNTA(MyList!$C:$C)-1,1)
Private Sub ComboBox1_Change()
ComboBox1.RowSource = Range("ListName")
End Sub
View 9 Replies
View Related
Jan 2, 2009
I have a combobox in which I need data loaded from a column of an excel sheet.
As of now, I am using the below
Last = Sheet9.Cells(Rows.Count, 1).End(xlUp).Row
With ComboBox1
For Row = 2 To Last
.AddItem Sheet9.Cells(Row, 1)
Next Row
End With
Is there any other way to load data?
View 9 Replies
View Related
Dec 4, 2007
My userform has a combo box that upon entry event is populated via a sub procedure.
The sub procedure defines a column of data on the active spreadsheet as a range and manipulates the range to add unique values only in alphabetical order into a collection.
Which the collection is then used to Add.Items to the combo box.
The userform allows selection of an existing value from the combo box OR type a new value In (no formatting on value).
When combo box is exited and a further command button is pressed the value from the combo box is added to the spreadsheet.
However, I don't need to refresh the source values of the combo box upon EVERY entry as I currently do, ONLY when a NEW value has been typed in and added to the spreadsheet.
How can I (possible check index value ?) ensure when the user types in a new value, exits field and presses the command button is the ONLY time when the combo box source values need to be refreshed.
View 9 Replies
View Related
Aug 30, 2007
I'm trying to change the source data for a worksheet for a series of charts. The source data appears in a series of tables. There are about 30 such tables and associated charts on the worksheet. (I need to duplicate this many times over, which is why I need to automate.). The chart names seem to increment by 2 (i.e. Chart 1, Chart 3, Chart 5) And the source data for the charts increment by 9: BM95:BP102, BM104:BP111, etc. How can I automate this? Also, I'm trying to automate changing the reference in the title of the chart. This reference increments rows by 9. When I use the macro recorder to do this, I get the following
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Range("BM95:BP102")
I'm unable to generate any code that allows me to change the reference of the chart title.
View 4 Replies
View Related
Feb 20, 2014
I need it without macros
Example : I create a list (cat, dog, cow), i create a dropdown on this list and select the first - cat. After that i change cat on SuperCat. Dropdown has a new list, yes, but selected value still Cat. Can i update it automatically on SuperCat?
Or are maybe there any shortcut for validation to let me know that i should change my value from invalid ( if the first variant is impossible )?
example.xlsx
View 8 Replies
View Related
Aug 31, 2007
I'm trying to create a chart and set its source dynamically.
I've tried two methods, the first by setting the XValues of a chart's series collection to a range specified by cells,
ActiveChart.SeriesCollection(1).XValues = range(Cells(9, coloffset + 4), _
Cells(9 + numvsteps, coloffset + 4))
And by using the chart wizard.
Call vchart.Chart.ChartWizard( _
Sheets(resultssheet).range(Cells(9, coloffset + 6), Cells(9 + numvsteps, coloffset + 6)), _
xlLine, , xlColumns, _
Sheets(resultssheet).range(Cells(9, coloffset + 1), Cells(9 + numvsteps, coloffset + 1)), , _
False, "End-End Eff., " & current & "A", "Line Voltage", "Eff. [%]", "test")
Setting the XValues property gives an "Unable to set the XValues property of a series class".
Using the chart wizard, trying to set the category labels parameter of the chart wizard to a range doesn't give an error message, but does leave the category labels section of the chart (when I click and view it) blank.
I've also tried
resultssheet = "Sheet1"
tempstring = "='" & resultssheet & "'!" & "R9C" & (coloffset + 1) & ":R" & (9 + numvsteps) & "C" & (coloffset + 1)
ActiveChart.SeriesCollection(1).XValues = tempstring
When I step through the code, tempstring is "='LineReg Results'!R9C1:R19C1", which is correct, but I still get an "unable to set the xvalues property of a series class" error.
Interestingly, if I record a macro of me setting the category labels, and play it back, I also get the error.
View 7 Replies
View Related
Oct 10, 2013
I have a userform with a textbox and would like the user to type inside the textbox which in turn send the text typed to a cell on my spread sheet say sheet 1 cell ref A1. I am writing the following into the control source Sheet1!A1 but the control source does not except this. I am using excel 2007 .
View 2 Replies
View Related
Apr 17, 2013
I have an 'in-cell dropdown' which looks up two values, Q1 and Q2 I have a range of data by month showing a specific number as below e.g
Month
Count
2012-01
120
2012-02
190
2012-03
200
2012-04
150
2013-05
175
I have a Line graph using this entire table as the source data. What I would like to do is only use part of the data based on what the dropdown cell specifies. For example, if it is set to "Q1", I would like just the 2012-01 and 2012-02 data to show in the graph. If its "Q2", I would like only 2013-03 and 2012-04 data to show in the graph.
View 4 Replies
View Related
Jul 29, 2013
I'm trying to create a userform with a combobox wherein items that will be provided in it will be based on the worksheet named NA. When i try to change the Rowsource on the properties of the combobox i'm getting an error "could not set the rowsource property. Invalid property value". Do I need to declare the name of the worksheet before changing the rowsource?
View 6 Replies
View Related
Aug 8, 2013
I have a pivot table whose data source is located on a shared network drive. Each day a new data source is uploaded and the only thing that changes is the date in the filename (ie. DataSource080813.xlsx). I am trying to find a way to set the data source to update with todays date automatically each day so that when you open the workbook it is always current days data. I have a cell (B2) with a formula in it that gives me the file location of todays file (ie. X:TeamFolderFile1DataSource080813.xlsx, where the date is derived using the TODAY() function. Is there a way to set the pivot table data source to cell B2 and have it use the text from B2 as the file location?
View 1 Replies
View Related
Nov 19, 2008
I have two worksheets in my workbook;
1. Table
2. Raw / Source data.
I have entered all the formulas into my table (sheet 1) referenced to the source data but I want to upadate the source data regularly but when I delete sheet 2 all the formulas come up with #Ref error. how i can update my source data so my table automatically updates
View 2 Replies
View Related
Jan 7, 2009
I would like not to have to update the source data for the pivot table in my database. I've read that you can solve this by using a Dynamic Named Range and using that as the source for the pivot table. That way the pivot table will expand as new data is added to the database. The formula I used to create the DNR is:
View 7 Replies
View Related
Oct 19, 2007
I have a pivot table created from a cube of data not within the file I am working in. I have created a macro that will update, but only for the specified timeframe. I have a separate worksheet within my file that will allow a manger to specify what timeframe he wants to see. Can I edit a macro to read from a source that is different than where the data is being pulled from?
View 3 Replies
View Related
Nov 10, 2006
I want a control to be updated when I scroll a scrollbar. The scrollbar is linked to "B25" and the control, tboYYWW2, is then updated via a HLookup of the "B25" value. By the code below I just get the control updated when the worksheet is activated and not when the "B25" value is updated by the scrollbar. How to make this work ? I also attach the workbook file I'm doing the tests in.
Private Sub Worksheet_Activate()
Dim varYYWW2 As String
Dim varWeekNr2 As Integer
varWeekNr2 = Range("B25")
varYYWW2 = WorksheetFunction.HLookup(varWeekNr2, Worksheets("EDUtest").Range("WeekNrWeek2"), 3)
tboYYWW2 = varYYWW2
End Sub
View 2 Replies
View Related
Sep 25, 2008
When one creates multiple UserForms with multiple (identical) TextBoxes, every control must have its own event handler procedures. All these TextBoxes in my workbook are to capture numeric data to populate various cells in the workbook.
Would you recommend using a Class Module to handle these events for TextBox controls, rather than having to repeat the event handler code for each control?
And if so, do you have some code that I can use that will cover most of the events and potential error handling routines for numeric input data?
View 9 Replies
View Related
Jul 19, 2006
I created a spreadsheet with several combo boxes that that is linked to other data.
When this data is sorted, the values in the combo boxes are automatically changed.
Is there any way to prevent the the values to change?
View 5 Replies
View Related
