I've created and coded a vba userform that creates purchase orders for my projects. The user begins by selecting the project code from the combobox (the project code is a unique identifier of each project). Once the purchase order is created, the information is logged in a separate sheet called "POLog" and the userform is cleared. The project code is saved in the first column of the "POLog".
My problem is that when I have more than one purchase order to create for the same project (sometimes I have 20 or 30), the combobox starts out empty and I have to manually select the project code from the combobox. Is there a way to allow the userform to recall the last project code that was used? Maybe recalling it from the last row in the "POLog" sheet?
I am getting close to finishing the drop down menu capability when filling in column L in tab Transactions. However, there is a snag. When I enter part of account say "fin" (the important part here is that the part of the word should not be the beginning of the account name) - then I select an account from the menu - but it does not stay in the cell if the part of the name is the beginning of the account name - all is fine.
Its a phone number directory. the data is retrived based on nickname. when a nickname is selected, its phone number and details will be updated in the textboxes ... example: tony (in A2) is selected from the combobox, his phone number (in B2) and details (in C2) are updated in the textboxes.
I have a spread sheet for equipment that gets loaned out on a regular basis. Cells B4 - B25 have combo boxes in them, with the options to select "On Loan", "In Stock Room" etc in them. What I would like to do is create something so that as the combo box is changed, i.e. from "In Stock" to "On Loan", a date changes adjacent to each combo box.
For example, combo box in B10 has been changed from "In Stock" to "On Loan". The adjacent cell records the date automatically that the combo box was changed.
I am trying to add a list of items to a combo box which is in a user form using vba. Find the file as attached. My code is as follows: when i double click on the combo box it opens editor.( 4th item: Frequency )
Private Sub ComboBox1_Change() With .ComboBox1 .AddItem "Y" .AddItem "M" .AddItem "D"
How do I populate a new userform from an entry selected in the listbox. The listbox only lists one item from the original table but I'd like to populate the new userform with related information from the same row.
I'd then like to save this information to a new sheet and store the original information on another sheet leaving the first sheet a table of information yet to be updated.
The attached workbook should make things clearer. The update button is the one in question.
I have a list of employees that can be assigned to tasks, the list is large enough that I cannot remember who is assigned to what.
All the employees assigned major tasks are automatically filtered out but these are employees with administrative tasks that should only be assigned as a last resort. I would like to assign the employees with administrative tasks a gray, italic, strike-out or some other font to indicate that they are different.
I assume this is done during the Add Item process...
VB: 'enables user to click [U]highlight and select[/U] an item in ListBox1 and ListBox2 item (same row in index) is also [U]highlighted[/U] (highlighted only not selected) Private Sub ListBox1_Click() ListBox2.ListIndex = ListBox1.ListIndex End Sub
Question: Is it also possible to enable a user to click to select an item in ListBox1 and ListBox2 item is also selected simultaneously (same row in index). Is there excel vb code to do this?
I think the code may be along the lines of the ListBox SelectedIndex property. What would be the Excel VB code equivilant for the ListBox SelectedIndex property, if so?
I have userform with a combobox that fills up with data when the userform is opened. One of the enteries in this combobox is "Test" I want to hide/delete this from the combobox.
I have a list of 20 cities in K1:20 and they can be selected from a drop down menu in column A2:A22. I want then to randomly pick 6 out of 20 and these 6 cities must to appear in a line A1, B1, C1, D1, F1, and G1
On cell A1 I have used the
IF ( A2=K1,K1, IF(A3=K1,K1...................A22=K1,K1,IF(A2=K2,K2......A22=K3,K3)
It seem to be an endless formula is there another way to make it easy ???
I am struggling with work-schedule worksheet and I want to do this:
In my attached sample worksheet are cells coresponding to a day of the month (monday to sunday, and so on...), and cell for job positions. Each day I must assign 5 employees to a different job position (job positions are on drop down lists).
When I select first job (on specific day), I want a pop-up window where would show what job I have allready selected and what job hasn't been selected so far.
How can I do that, is It possible in VBA coding, maybe with Listbox or CheckedListbox.
I have 1 macro that i would like to be activated as soon as i select any item from a form control listbox (doesn't matter which item). i am not using an active X control but rather a form control.
I'm trying to add items to a Combobox on a Userform dynamically when the form loads - the criteria is if a cell value is greater than zero, the value being calculated by a formula within the cell.
Code: If Sheet5.Range("B58").Text >= "0" Then .AddItem "Target" End If
I have a userform called "Description_Form". I had a listbox on this which worked 100%. I then decided to replace the listbox with a combobox. I replaced the word listbox1 with combobox1 in teh code below and now I am getting an error message:
Run-time error '-2147352573 (800200003)':
Could not fine the specified object.
I have checked the name of the Combobox and it is definitely combobox1.
Sub PopulateListWithHorizontalRange(New_Dest As Variant) Dim x As Range Description_Form.ComboBox1.Clear For Each x In Range("S_DESTINATIONS").Cells 'here is where I get the error message Description_Form.ComboBox1.AddItem x.Value
I have, 10 combobox, if the user makes click in the combo,start the event combobox1_change, and the value of the combobox is searching in excel, when find it, move one cell toward the cell of the left, and the value of the cell of the left is shown in a label, that work.
But I need copy teen time the same code? (My english is very bad) this is the
Private Sub ComboBox2_Change() If Sheets.Application <> "Materiales" Then Sheets("Materiales").Select End If
I have a userform which manipulates data based on the userselection from the combo box. I have setup the userform so that the user may select up to 3 sheets due to the presence of 3 combos boxes. I need to writing an IF statement which checks to see if combobox1 is occupied to carry out a function, followed by it checking to see if combobox2 is occupied to carry out the function, and then checks to see if combobox3 is occupied and carries out the funciton.
As such, if only 1 combo box is occupied it would then only carry out the operation on combobox1's selection, and if none are occupied, nothing occurs, the box simply stays open. This is what I have so far, I know there is probably a more eligant way of writing such a If/Then/Else statement
Sub Start() If UserForm1.ComboBox1.Value And UserForm1.ComboBox2.Value > 0 Then Call Find1 Call kTest1 End If
If UserForm1.ComboBox3.Value And UserForm1.ComboBox4 > 0 Then Call Find2 Call kTest2 End If................
The ideal is I have a list which the use fills in, for sack of argument Goal 1, Goal 2, etc but I have a problem. This list which the user builds I want to appear in a combobox with is quite easy using the list function and naming the range.
The problem I have is that Goal 1 or 2 can be in this list more than once, if it is at all possible I want or would like the Combobox to only show Goal 1 once and not twice or how many times it occurs. I require the Combobox just to show all Goals once no matter how many time they occur.
How do I check the information from a user selection of all comboboxes on a multi-page control to format a worksheet?
A few more specifics: There are roughly forty comboboxes on a multipage with six tabs. The comboboxes contain a list of choices for how different aspects of the project are financed. I want to check for whether the user has selected a specific entry. If any of the forty comboboxes have made that selection, some code runs that formats the column of the worksheet in a specific way. I have written the code which formats the column, and it works fine, but my attempts to run the check mentioned above, have not worked. The code cannot check based on .listitem, it must check based on a specific string.
A few more clarifications: It doesn't matter whether all forty comboboxes have this selection, or one; if any of them have the selection, the code needs to recognize this. The code would currently run off a command button which performs a series of calculations, tests, and then runs the code to format the worksheet.
I have two comboboxs on a userform, both are populated like this: (ComboBox1 is a different sheet/column)
' Sets Remarks in ComboBox4 Contents With Worksheets("Data") Set rng = . Range(.Cells(1, "C"), .Cells(Rows.Count, "C").End(xlUp)) End With With ComboBox4 .RowSource = rng.Address(external:=True) End With The function is that ComboBox1 will populate the names on lets say Sheet1, Column A, and when selected will populate by offset all the other Textboxs, and ComboBoxs.
Textbox1 is a date ComboBox4 is populated off of items from the data sheet ComboBox1 is populated off of sheet1 and provides names, then fills the userform fields
In populating the Userform, it fills Combobox4.value by the offset value of the selected name.
That cell does not contain the same info that was loaded into the ComboBox initally, and it does not show it. All other ComboBox entries match preloaded values, and show.
How do I get the ComboBox to display what is in the OffSet cell value, rather than blank because its different?
The below is causing me an issue as it is setting the ws value to equal nothing instead of sheet1 or sheet2 etc. I have my dimensions set above and there are no spelling mistakes.
It falls down on the VB : Set LastRow = ws.Range("a65536").End(xlUp) line but this seems to happen because ws is set to nothing.
VB: For iramp2 = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(iramp2) Then Set ws = ActiveWorkbook.Sheets("Sheet" & iramp2) End If
I am a softball coach and I want to create a fielding roster for my team. I have all the players names in column A (A4:A14). I have positions listed in column L (L4:L15) as follows: P, C, 1B, 2B, SS, 3B, LF, LC, CF, RC, RF, X. Cell range B4:H14 (7 innings) is where I need to have drop downs, but once I select a certain positions for one player in inning 1, for ex., I want it to not be available for the next player in the same inning.
how I can have the selected item in a dropdownlist also to be shown in another cell? Typing the cell number of the dropdownlist in another cell (f.e. ="dropdownlist cell number)" doesn't seem to do it.
It seems that when reloading a listbox (in my case in ppt, but triggered from vba in excel) the ppt application (office 2010) was not highlighting the selected row in the listbox, and was infact also losing the selected row information when the item lost focus.
indication in excel that new data is to be displayed triggers macro in ppt to fetch data and update:- 1. select the userform and listbox 2. fetch the data from excel worksheet range into a local array 3. save locally the current selected index in the listbox 4. reload the data into the listbox and adjust column widths 5. relocate the old selected item, and set the new selected index
Summary of key Code lines which did not work:
[Code] .....
Code which worked fine:
[Code] .....
So in summary, if you get problems with listbox selected items not being highlighted, double check you are not re-defining column widths after loading the data into the list.....
I have a userform that search for a value in column A, it displays the results in the listbox. Example: Search for value "111" and it brings me back the following results back in the listbox "111 David 35". So this means 3 columns matching data is returned.
I want the following to happen if I double click on the item in the listbox it needs to update the value selected in the worksheet eg. strikethrough the row on the sheet to show item has been selected/done.
