this problem is just an follow up question to this thread [Solved] VBA : user input to combobox xla. it supposed to be a follow up question but as per RULES is concerned i have to post a new one "4. Never post a question in the Thread of another member. You MUST ALWAYS start you own New Thread.". my question is how to retain the inputted values upon next opening of the workbook? you see that in this thread everytime the userform initialize the values added are also refresh , but what i want is after i manually input an entry of the combo box on next open the values are saved correctly..
I'm trying to populate a collection with PossibleAnswers to a Question. Every time I do this the values from the newly added entry get propagated to each entry in the collection (best way I can describe it - see code attachement).
Here's the existing code - including the current debugging code surrounding the add! The function is a Method of a Question object.
[Code] ....
All the Find and FindNext procedures do return the appropriate next value. However, the debug loop produces the following:
[Code] .....
As you can see, something is being added, but it seems to be affecting every single entry.
how to make all cells displaying an item from the list source update with new information added to list source.
Example: Cell "A1" on worksheet 2 displays the 3rd slot of information from list source on worsheet 5. I want cell "A1" on worksheet 2 to update when the list source information in the 3rd slot on worksheet 5 is changed.
Notes: The list is set up by naming the columns on Worksheet 5 and is set as a validation list on the other worksheets. So on Worksheet 2 column "A" has the validation
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".
I want to create a macro that when executed changes the name of "File1" to "File1 - Edited 8-9-2012". With the date being today's date (variable). This file is going to be emailed to someone who will then execute this macro.
Is there a way to prompt the Save As window with the updated file name so the user can save the file where ever he/she wants?
I am running the below code to basically apply a defined list to a cell containing the string "WORKDAMNIT". This list is used as validation. I am running into a HUGE problem where once I apply the validation on about 17,000 cells and try to save the spreadsheet I get an error saying that "Excel could not save all the data and formatting you recently added to...". Thus, the IF loop basically does a save of records everytime I hit about 1000 records. I can even see the file being saved. However, whenever I close the spreadsheet it asks me to SAVE!! If I try to save it I get the same error and if I dont NOTHING gets saved.
Sub Valid()
Dim listCount As Long 'counter Dim cellCount As Long 'cell Counter for save function Dim foundCell As Range 'found cell in sheet Find Dim foundList As Range 'found cell in sheet list Dim fwb As Workbook 'workbook value Dim fsheet1 As String 'find sheet Dim fsheet2 As String 'list sheet Dim strMatchCol As Integer 'address value of column number Dim col As String 'actual value of column string Dim strFind As String 'The string we are searching for (eg. "blah blah")
I have in "column A" an accountnumber and in "column D" a lastname. The lastname can appear more than once and the accountnumber also. Though I want combobox1 on userform1 to contain the lastnames. It's possible to contain a double lastname as long as the accountnumber isn't the same.
For instance: accountnumber: Lastname: 1234 Johnson 7890 Johnson
here Johnson will be insert twice into the combobox.
accountnumber: Lastname: 1234 Johnson 1234 Johnson
I'm trying to take a list of names from a worksheet and create in a userform a combobox that lists the names.
Private Sub Combo_Box_Leadoff_Click() Dim counter As Integer Combo_Box_Leadoff.Clear For counter = 3 To 16 Combo_Box_Leadoff.AddItem Worksheets(5).Cells(counter, 2).Value Next counter
In the properties window, I set the value equal to the first name in the list. The above code yields a combobox that is blank except for the value set in the properties window. When I do not set an initial value, I get a completely blank combobox. I'm pretty new to VBA, and I've manged to figure out all of the macros I need so far, but setting up the user form has proved surprisingly difficult.
This is a paricular problem I'm having in the middle of lots of problems, hoping you can help.
I had created a macro to transfer information from a sheet into a seperate sheet, for storage.
I have sinced changed the data entry sheet to use comboboxes instead of normal text, as it was important to ensure the text is entered identically each time. Now, however, my macro transfers the numerical position on the list of data for the combobox instead of it's value
I am populating a ComboBox with entries from a Range. The entries have a different Interior.Color depending on whether the individual project represented by the entry is complete. If the entry is complete is has an Interior.Color of blue; if not, it is red. I want to populate the ComboBox with both, but if the Interior.Color is blue, I want to change the font.color displayed in the ComboBox to blue. Just the color of the completed (blue) entries. The other (red) entries can have a normal (black) font. Is there a way to do this?
Here is the code I have been working with, but nothing I have tried has worked. Maybe another strategy, or whatever.
LastRowTF = Sheets("Shipsets").Range("F65536").End(xlUp).Row Set myArray1 = Sheets("Shipsets").Range("F2:F" & LastRowTF) For mA1 = 1 To myArray1.Count If myArray1(mA1).Interior.Color = 255 Then
I have an worksheet with a UserForm. On this UserForm is a ComboBox with the RowSource set to a named range called lVender. This ComboBox is setup to post & sort new entries into lVender apon exiting the ComboBox.
If a mistake is made and it is not caught before exiting the ComboBox, then I need a way to remove the bad entry from the lVender list.
I have included a small sample to better show the problem.
I have a user form with a group of several option buttons. When an optionbutton is selected, programatically, I want a worksheet's named column to be parsed through to find unique entries and then have those entries passed to the form's combobox. I've spent the last hour searching for an answer but to no success
There are two Combo boxes in the form. ComboBox1 is being populated from a named range - "ParticipantName". (Located in Sheet1!C2:C500) ComboBox2 has been populated with "No" and "Yes" with additem in Form1 Initialize.
There are 3 (Three) Command Buttons - "Save", "Cancel" and "Close".
"Cancel" shall clear data in both Combo Boxes. It is done. "Close" shall unload the Form. It is also done.
My problem is with the "Save" button. The user will select a name in the ComboBox1 and Select "Yes" or "No" in ComboBox2. Then if the user press "Save"; only the selected data of ComboBox2 will be written in the 26th Column (Column "Z") of Sheet1 in respective Row of the name selected in the ComboBox1.
I've got a userform with a combobox and a textbox. The combobox is populated from a dynamic range of product numbers and when a existing number is selected from the list, a respective product name appears in the textbox. These are then saved to a database sheet via command button.
My problem is following:
When a new product number is entered in the combobox that isn't on the list, I want the number & name of the new product to be saved not only on the database sheet but also on the source sheet so that the new product would be included in the combobox list when run again. Here's my code so far:
Private Sub CommandButton1_Click()
'here is the code that saves everything to the database sheet
I'm using Windows XP with MS Excel 2003. I have a pivot table representing a survey. Let's say I've built the survey outside of excel and I've imported the response data into Excel. One of the questions in the survey is "ratings" and the possible valid responses for it is: "Excellent", "Good", or "Poor". In my data set in excel let's say I have 10 responses or rows and all the responses for the question on ratings are either "Excellent" or "Good". (There are no rows with a "Poor" value in the ratings column).
For example, let's say out of the 10 responses, 6 are "Excellent" and 4 are "Good". As such my Pivot chart shows two bars: one for the number of respones with "Excellent" (10) and another bar for the number of responses with "Good" (4). My delima is how to show a third bar showing "Poor" with a zero as the number of responses.
I have set of user-form contains with Combox & 2 textbox and to generate report one cmd button
I have 3 different sheet contains report of daily activities ( Dispatch,Closed,Cancel)
If Dispatchcalls Select In Combobox1 ,Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from dispatchcalls Then Save Data Into Excel File As "Dispatchcalls".
If Closedcalls Select In Combobox1 Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from Closedcalls Save Data Into Excel File As "Closedcalls".
If Cancelcalls Select In Combobox1 Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from Cancelcalls Save Data Into Excel File As "Cancelcalls".
"C:UsersmaniDesktopNew folderLenvo_ReportsONSITE CasesVlokupuf" This is path i stored existing 3 file dispath,closed,cancel
Using the developer tab I inserted an Active X combo box. Under properties I referenced a 'linked cell' that I want the data to appear in. This box works great as long as I keep the sheet open. Once I save and close the workbook and re-open, that reference is lost. It shows #REF! in that field. Why does it lose the reference? I have tried to reference a cell on the same sheet as well as a cell in a different sheet and it keeps the sheet name but not the individual cell.
I manage messaging server farm that keeps mailboxes and their replicas on different servers for resilience. I need a way to check the replication status. I try to do it by comparing their sizes. I got all mailboxes and replicas incl their sizes imported into excel (2010) and I need a way to compare them.
Like this: name size
John Smith 10
Mary Westwood 20
[Code] ......
My idea was to find matching pair (two records of John Smith) and compare size. If the size differs more than specific value (5% or 2MB for example) the result is true/value/colour by conditional formatting etc.
I'm entering some time data (in format hh:mm) into Columns. Pretty basic Workbook that I have been given and, to be honest, it's just donkey work putting the data in. The times going down the Columns need to be progressive (ie B8 is arrival time, B9 is admission time etc). This goes through to B27 and then repeats from C8-C27 and ultimately J8-J27.
I'm looking for something as a formula to pop into Column K (or VBA, I don't mind!) which will do a simple verification to ensure that the times I enter is not less than the any of the cells above in the aforementioned ranges. I can do simply the cell above, but due to rusting of brain, can't think how to do this for the range!
We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.
Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.
The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.
The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.
We can't figure out why the macro takes longer to run when no changes have been made?
J_Database, a prototypical database J_ComData, a custom worksheet that prints out an invoice-like document.
I have a combobox (cboName) in a userform that is populated by a dynamic range from J_Database. Once I hit my command button, I would like the program to loop through all of the records, one-by-one, and input the values from the combobox, and drop them in cell A1 of J_ComData.
From here, the remaining information about the combobox value from J_Database will be retrieved and J_ComData will be printed.
I was working with the following code:
Code: Private Sub cmdPrint_Click() If Trim(Me.cboName.Value) "" Then 'Unhide the printable paradigm sheet Sheets("J_Pref").Select Sheets("J_Para").Visible = True 'Drop judge paradigm userform information in the sheet Sheets("J_ComData").Select
[code].....
This code works if I select just one name, have it populate A1 with that name, and then print out the document. However, writing the code so it will just print through all of them at one time.
I have this long list of data (company name, company ID and OIC) on sheet 1.
Then on sheet 2, user will key in the company name manually then the company ID and OIC name will auto-populate (i did the match index already on this one) before filling in the other cells.
How do I make the cell to auto-complete the company name by using the list of data in the other sheet? Do I use the combo box? If I use the combo box, will it affect the match index formula i created for company ID and OIC?
I have a worksheet in which i have a set of data similar to the following (i have used colons as delimiters in this example).........
Basically what i want is 2 combo boxes. The first combo bow will allow you to select the phone type (e.g. Motorola PEBL, Motorola RAZR etc etc). The second combo box will have a list of items which match up to the value selected in the first combo box. Once the item has been selected from the 2nd combo box then the relevant matching data will be shown in a group of cells on the worksheet on which the combo boxes reside.
I know that this could be done by using the data validation feature if my data were laid out in a different way however it is not possible for me to re-lay out all the data in my worksheet. I have been told that there my problem copuld be potentially solved by using Excel VB but unfortanately my VB skills are quite weak.
I have an inventory sheet that uses multiple userform comboboxes to input data to a sheet. Everything works great except on one userform "pullmat" where the user removes inventory from a unique list of all material available in "Master Log" sheet. The combobox that im trying to fix is "combobox3" in the "pullmat" userform. I'm trying to get that combobox to only show P.O. numbers that are related to the material selected in the "Material Code / Name- combobox2. In other words, the user should only be able to select a P.O.# that matches that specific material on the Master Log sheet.
The file size is too large to attach but I can email a copy if needed.
I'm running in to some problems on creating a Userform. I will try to explain it as best as possible. Right now I have a userform set up with 2 ComboBoxes. Here is my current code. It's not near complete.
Private Sub CommandButton1_Click() Set ufStart = Worksheets("Data"). Range("AP4") Set valNames = Worksheets("MasterData").Range("AA6") Set valMonths = Worksheets("MasterData").Range("H3") Set SelMonth = ComboBox2.ListIndex Set SelName = ComboBox1.ListIndex If TextBox1.Value > 0 Then ufStart.Offset(SelName, SelMonth).Value = TextBox1 Else: End If End Sub
The way it's supposed to go, is if TextBox1.Value is greater then 0... then go to UfStart and Offset by Row, which is ComboBox1 Selection Index Value and by Column, which is ComboBox2 Selection Index Value. I can't get the sub to get past Set SelMonth = ComboBox2.ListIndex.
