i am making a phone company site on excel, with vba, for my college project, i need to know how to make it so wen i change the selected phone from the listbox, 2 show the image of the phone in the image box, from my files.
View 9 RepliesI have a series of dependent drop lists and a cell which asks for a phone number. I would like if the selection in the previous cell is North America, the format of (111) 111-1111 is chosen. if the user enters the improper format I would like them to be prompt with an error "Improper Format - <Proper Format>", clear contents, and re-select the cell otherwise if valid select the next cell. It would need to strip out the leading 1 incase an international code is entered and also special characters such as "(",")","-","+","_" as well as spaces.
The next problem is that it would have to change format to if another selection is chosen. The other two selections are Australia and Europe. Europe is more difficult of a format as the area codes vary in length. I will be dealing predominantly with Sweden whose area codes even vary. For Australia I would like it to be in the format 011 61 1 1111 1111 where 011 61 will be added even if the user does not enter it (or if not possible prompt with proper format).
The cell asking which continent is G35 and options are "","Australia", "Europe", "North America"
The cell asking for a phone number is K47
The next cell on valid response is G50
I am using this code to hide or unhide rows of text on another sheet:
VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)
Dim sAddress As String
Dim sValue As String
'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)
[Code]....
When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.
The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..
Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)
Code solution can be entered directly beneath:
VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True
PHONE formatting. Is there a way to create a macro to format the VALUE of the phone to be either (XXX) XXX-XXX or XXX-XXX-XXXX ? I obviously can get it to look like this, but the value is just XXXXXXXXXX.
View 13 Replies View RelatedI want to add the selected value from listbox to textbox.
View 6 Replies View RelatedI have a spreadsheet with names of students, year groups, subjects and others. My VBA code will analyse student's results, but before I get there I would like the user to be able to select groups of students using listboxes.
Now I have a tonne of variables that I won't bore you with but the main thing I would like to do is to scan through each cell in a range on my spreadsheet and determine if the value of that cell is equal to a selected option in the listbox. This will let me, for example, filter by only Year 10 students, or select to display only Year 9 and 11 students.
I already have the code to scan through each cell:
VB:
'Run through each student and check they match the criteria
For N = 1 To StudentList.ListCount
'Split the first and second names
StudentName = Split(StudentList.List(N - 1))
[Code] .....
Essentially, I would like to take the cell "A" & N2 and check to see if it matches one of the selected options in a listbox called "YearList"
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.
View 5 Replies View RelatedI can add an item from one list box to another using the following...
[Code].....
But I want to be able to remove the item from the listbox by clicking it. Tried this but doesn't work!
[Code] ......
I have a (MultiSelect) ListBox1 that is populated with Customer Names for the user to select from. I want the user to be able to hit the CommandButton1 adjacent to the ListBox1, and the selected Customers copy into/get added to the ListBox2.
I have the following code, adapted from other code, but it is not working:
[Code] ........
Sorry, should be a simple one...
I can add an item from one list box to another using the following...
I have a userform running with a number of text boxes, as well as one listbox. To extract a value from a text box I obviously write something = me.textbox.value. How do I do the same for a listbox (single item) as me.listbox.value produces an error!
View 1 Replies View RelatedI have a form control listbox, which is populated with filenames from a specific directory. When the user selects an entry I need that text to appear in a cell in the workbook. This is then concatenated with a folder path to open the file to copy and paste some data out into another workbook. I have tried...
Code:
Sub GetWrbkbkname()
Dim strlist As String
strlist = Sheet1.Listbox18.Text
Sheet1.Cells(1, 1) = strlist
End Sub
But receive the method or data member not found error. The cell link property just returns the position in the list! I need the text!!
The only workound I could think of was populating a space in the SS with the directory contents and using the cell link value in a choose function to return the selected workbook name.
I have a listbox on a userform, with multiselect set to 2 (Multiselect extended). When the listbox is displayed for the first time, no elements are selected. At a certain point in this application, I want to get back to the original state and deselect all items.
I do that with the statement Listbox1.listindex=-1. I hope that is the right thing to do. However, the elements that originally were selected are still blue-coloured, like they became when the user selected them. How do I do to make them look deselected?
It also seems to me that the array Listbox1.selected(x) still holds the value "true" for elements that were selected. Is it so that in addition to say listbox1.listindex=-1, you have to loop through the elements in listbox1 and set all of them to false?
I have a couple of listboxes and use the mouseup event to do stuff with the row that's clicked.
When a new listbox is clicked I'd like to remove the highlighting or selection from the last one.
I thought this would do it
".Selected(x)= true" where x would be that listbox's list index. But no.
Is there another way?
i would like to paste also the rows that selected from listbox i use a userform and also use multiselect listbox
Code:
Private Sub frmstartbtn_Click()
Dim lItem As Long
With Me.frmListBox1
[Code].....
I have a workbook with roughly 25 sheets, each sheet represents a customer. Each month, I want to be able to run a macro in the workbook that will produce a UserForm containing a Listbox of each unhidden customer (worksheet) in the workbook. After I select all of some of the customers, hit a "process" button which will run a macro on each of the selected customers from the UserForm one worksheet at a time.
I currently have code written to produce the UserForm and populate the list, but I am uncertain how to write the code for the "process" button to run the macro on each selected customers one at a time. All I know how to do is have excel select all the chosen customer worksheets all at once.
I have a listbox with several items selected. Is there a way to automatically show the selected items in the worksheet (as values in cells)?
View 8 Replies View RelatedI have a listbox that is automatically filled with data in two columns through a lookup function from a worksheet. That works fine. But now I want to fill the third column with data through a text box, but only in the rows I have selected in the listbox.
My code so far is:
[Code] ....
The number I want to insert into the listbox appears, but not in column 3 in the selected row(s).
How to create simple macros assigned to button to extract the selected items from the list box to cells
Check the attached file : jjjj.xlsm
If I select y2 and y7....will copy these two selected to the next sheet "sheet2" but i want to paste it at the last used cell in column A
Additionally....code to reset the selected items from the listbox.
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.
Code for my listbox populate:
[Code].....
I am trying to take selected items from a listbox and put the selected items into an array. Basically I am trying to put the selected items into a variable that I can pass to other modules. I am close but something is off. I can't tell if it's the variable declaration or the code.
I get Run Time error 91. Object variable or with block variable not set
Here's what I have so far...
[Code] ......
I've got a listbox (ListBox1) and it lists 5 files. names are One, Two, Three, Four, Five (all .xls) and they're saved in C:/MyFolder
What i want to do, when a user clicks a button, for a code to only open the files that are selected. So for example, the user selects Three.xls and Five.xls - the code would need to ignore the others and open the selected files.
How can I adapt the following code to only show Columns; A, B, E, F and J from Sheet(3) into a Listbox called lbx_LiveAllocations?
As it stands, this code is only adding Column A. My only alternative has been to add all columns on the worksheet to the listbox, however there's a lot of unneeded information between.
Code:
Dim LR As Long
Dim ctrl As Object
Dim i As Long
Dim dic As Object
Dim arr As Variant
Set dic = CreateObject("Scripting.Dictionary")
[Code] ........
Erase arr
Returning items selected from a ListBox. I have a Multiselect Listbox with 15 items. Additionally, I have a worksheet with 15 columns (each one corresponding to the 15 ListBox items). I'm looking for code that will do the following:
After a user selects values from the ListBox (can be more than 1, thus the Multiselect), I would like for "TRUE" to appear in row 2 of the worksheet for each column. (IE: Column A is for Bikes, if a user selects "Bikes" from the ListBox and hits a CommandButton, I would like for A2 to say "TRUE".
To this point I have been able to successfully write code that will save a constant set of worksheets as a pdf. However, I would now like to alter it to be able to dynamically select the desired worksheets from a list box (I have been able to populate my list box) and then save as a pdf. The last step is where I am have issues. This is what I have thus far..
Dim relativePath As String
Dim Selected As Long
For Selected = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(Selected) = True Then
Sheets("Summary").Range("Q65536").End(xlUp)(2, 1) = ListBox1.List(Selected)
ListBox1.Selected(Selected) = False
[Code] ..........
I have created a custom Menu (excel add-in) to make my work easy in excel. My problem is to print only selected sheets from Workbook in one PDF file, for that I've created a Userform with 2 listboxes, add sheet and print buttons. In the first listbox are listed all the sheets and in the second listbox are the sellected sheets to be printed. What I've succeded so far is to print selected sheets, but it creates one PDF file for each sheet, only if I put my code in workbook and not in Menu add-in (.xla file). As PDF Printer I use PDF reDirect Pro v2.
View 8 Replies View RelatedI have listbox of information set up to have a user choose several items. I want the items to then transfer to another worksheet. I am using the code I found here:
Private Sub CommandButton2_Click()
Dim lItem As Long
For lItem = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lItem) = True Then
Sheet2. Range("A65536").End(xlUp)(2, 1) = ListBox1.List(lItem)
ListBox1.Selected(lItem) = False
End If
Next
End Sub
But it is only moving The first item in a column, instead of all the columns of information. What do i do to get it to move all the information??
When i tranfer my listbox content to sheet the selected item remain selected in listbox
Dim i As Long, j As Long
For i = 0 To Me.ListBox2.ListCount - 1
If Me.ListBox2.Selected(i) Then
j = j + 1
Worksheets("Workings"). Range("AA" & i + 2).Resize(1, 1).Copy Worksheets("Print").Range("B" & Rows.Count).End(xlUp).Offset(1)
ListBox1.Selected(i) = False
End If
Next i
The following code populates a forms' second listbox (Elements_lbx) with True. No amount of diddling (by me) can get the selected items from form's first listbox (Content_lbx) moved over to the second. (NB. the list box isn't the standard VBA listbox but an open source: ListEX from Marco Bellinaso.)
Private Sub CommandButton2_Click()
Dim lbx_Sel As Long
' loops through ListBox to test if it is selected
For lbx_Sel = 0 To Content_lbx.ListCount - 1
If Content_lbx.Selected(lbx_Sel) = True Then
Me.Elements_lbx.AddItem Me.Content_lbx.Selected(lbx_Sel)
'
'Clear the selected item
Content_lbx.Selected(lbx_Sel) = False
End If
Next
End Sub