Hi Andrew! I just wanted to say that even 4 years after you uploaded this, I have found immense use out of this video and the previous ones. I wish I had found this series a month ago to be honest with you. Thank you for such a clear, concise, and descriptive video!
my final way of concatenating items from an array when using vba was: SelectedGenres = SelectedGenres & iif(SelectedGenres ="", "",",") & FilmGenres.List(i) this will prevent managing the trailing comma at the price of an extra comparison in when ok.
My knowledge of lists boxes, especially multi-select list boxes was lacking. I have a much better grasp on them now. I'm familiar with current region, however I didn't know that you could click on a cell and use control + A to select the current region. What a great tip for showing someone how a blank row or column can change the current region in a worksheet. Thanks Andrew, you are an excellent teacher.
Hello Andrew, it's been great watching your videos, so much good insight to my vba journey. I have a question I hope you can answer. I've added items into my listbox using the rowsource from my worksheet. It works perfectly fine and I'm happy with it but I want to be able to sort the list box (alphabetically or by date in a certain column) without sorting the the worksheet. I hope you can help help me out as I've tried many things but can't seem to get it right.
Hi Andrew - fantastic video as always. Appreciate it much. Could you kindly tell me if you have done any video on the list view control? I searched but couldn’t find. Many thanks in advance.
Hi Andrew, is there a way to insert into a listbox a select all item tickbox which would work in a way like e.g. as it works in Excel filters? I tried to make one utilizing the change event of the listbox but my code got complicated and ended in an infinite loop.
Hi Mark! Yes, a bit tricky to make sure that changing the options programmatically doesn't trigger a cascade of the Change event! Here's some basic code for a form with a single list box called ListBox1 Option Explicit Private ChangeEventDisabled As Boolean Private Sub UserForm_Initialize() With Me.ListBox1 .AddItem "Select all" .AddItem "A" .AddItem "B" .AddItem "C" End With
End Sub Private Sub ListBox1_Change() Dim i As Long 'Exit sub if events are disabled If ChangeEventDisabled Then Exit Sub 'Check if Select All option has been changed If Me.ListBox1.ListIndex = 0 Then
'Prevent subsequent changes from triggering this event ChangeEventDisabled = True 'Loop through Listbox items except for Select All option For i = 1 To ListBox1.ListCount - 1 'Set Listbox item to same status as Select All option Me.ListBox1.Selected(i) = Me.ListBox1.Selected(0) Next i End If 'Re-enable change event ChangeEventDisabled = False End Sub
Hi - I really like the way you explain things. I wonder if you have any ideas about a problem I am trying to solve. Its about being able to manipulate background color of list row using vba. Is it possible to program several background color per line in listboxes? In my userforms I have listboxes and I get their input from named ranges - which I populate via queries. so each row of a list box contain multiple cells from that named range. Is there a way to program the background coloring of cells so that the listbox would show them? currently I paint the background of cells in named range with vba and when I navigate to it or when I export that named range to a different workbook - I can see that color background worked fine, But when I look at same named range thru the listbox - the background colors do not show. Any ideas on how to overcome this problem?
The application I am building will allow the User to set up new lists that are not yet populated. For Example, I will create a Table for a Named List called 'Cost Codes'. Initially the Cost Codes List will be unpopulated (will have one blank row); waiting for the User to start inputting their own Cost Codes with Descriptions. With the .End(xlDown) method, I find that the Code fails, however; if I have the one Row with data in it, then the Sub runs fine. I am a very rank amateur to Excel VBA, hence why I am very appreciative of your Website & UA-cam content! Thank you! Will it be necessary to write a Sub that will have the end User initiate the Table, Name the Range, and then enter their Cost Codes... I hope I have explained this well enough to be useful for you. You input is greatly appreciated!
Hi there, it was beautifully explained! Using the End property with an unpopulated list is a common way to experience run time errors! So much so that I made a separate video on various ways to find the last cell which you might find useful ua-cam.com/video/pI2px2KoapU/v-deo.html I hope it helps!
Hi Andrew! The functions part of VBA is a bit confusing; I have gone through the introduction part in one of your videos on function. Need your suggestion on how to go about..thank you in advance!!!
Very nice tutorial.. Lot loves from India. I learnt many tricks and tips from your channel...❤❤ I have one doubt In the vba list box I have few items in that I want to highlight one particular item with red or some other colour. Could please help me out in this
Hi Andrew, hi all, Your videos are great as always, thank you Andrew! When watching at this video, in the "if" you write at min 12:30, I was wondering how to avoid the empty case. So I thought by writing the following code in the "if", it might be another way to make it : If SelectedGenres = "" Then SelectedGenres = FilmGenres.List(i) Else SelectedGenres = SelectedGenres & "," & FilmGenres.List(i) End If ... or would this be longer ? In any case, thank you very much for your clear explanations, examples, and vast knowledge you give in the videos.
Anytime I use the scroll bar to scroll down my ListBox, and then try to select an item, it automatically scrolls back to the top and does not select the item. I have created a ListBox where I have over 350 items, so I also added a search function, and when you select an item, you have to click a command button which moves it to a TextBox, creating my end-result list. I think this is similar issue as is with the scroll bar function in other videos, but I cant seem to find any information to verify this. Also is there a way where I can say “If ‘in listbox’ “XYZ” is selected and added to textbox..” no other values can be added? Maybe disabling the Add Selected Item button through an if statement. Any help would be greatly appreciated, and as this is a work related project, the speedier the better. Thanks
Hi I have a doubt .Like if we have both text and some cell with borders looks like image.so we need to select it as range and paste it in listbox .Is that possible ?
Thanks For This Series But I Have A Question. I Want The User To Select A Genre (Like You). When They Don't Select Anything And Click 'Add To List' Button, It Turns Pink Successfully, Indicating That It Must Have Some Selection. But When I Select Something The ListBox Is Still Pink. Is There Any Way That After Selecting Even One Item, The List Box Turns White Again. (Which Event Of The ListBox I May Use?) Thanks In Advance
Hi Andrew, is it also possible to write the genres not in one cell but below each other? I know that is has nothing to do for your video. But iam googled a lot and i didnt find it. So why not asking the VBA Guru ;-) I tried: SelectedGenres = SelectedGenres & FilmGenres.List(i) & VBNewLine SelectedGenres = Left(SelectedGenres, Len(SelectedGenres) -1) 'To clear the last VBNewLine. In my msgbox everything is filled in nicely (below each other) but when i write my code: wsTest.Range("A1").Value = SelectedGenres Everything is filled in in the same cell. Its like when i press ALT+Enter when writing in my cell. :( :( Is there a solution? Thank you so much for al the other video's as well!! Now you made want to buy Excel 2016 :o ;-)
This worked for me. The references do not have anything to the with film generes, because they are from one of my own projects, but the idea behinded should work just fine. Therefore i = 16 is just the row that I would like my listbox input to start being inserted form. Insert a private sub insted of a private function, and call it in the sub AddToList_Click. Private Sub ChooseProduct() Dim LoopCounter As Integer For LoopCounter = LBound(ListBox1.List) To UBound(ListBox1.List) If ListBox1.Selected(LoopCounter) Then i = 16 While Cells(i, 1) "" i = i + 1 Wend Cells(i, 1) = ListBox1.List(LoopCounter) End If Next LoopCounter End Sub
Anybody that could please let me know if there is a way to limit the number of items a listbox can contain?.I mean I´m triying to have a listbox with only two items no more than them so I would like to code a constraint.
Finally doing some coding with Userforms but having some trouble with listboxes. I have 2 listboxes on the form. I load the boxes with values from a data list on a worksheet. My trouble is that once I initialize the form and add values to the lists, one of the list boxes show a listbox.value of NULL. The other shows a listbox.value of "". They also behave differently when I try to set a default value in each (one accepts the default value, the other does not) but I can't see any difference in the way they are configured. I've tried deleting/recreating, with the same result. Any ideas?
I populate the listboxes in the userform_initialize event from values in a worksheet using a for-each loop. Works fine. After the loop I then try to set both listbox.value properties to values from the same worksheet range.(I do this so if the user does not select a value then the listbox.value won't be an empty string). If I run the code and hover over the listbox.value properties before I try to set them, but after the listsboxes have been populated, one of the listbox.values is set to "NULL", the other is set to an empty string"". After I try to set the values, the one that started out as "NULL" accepts the value, the other does not but still shows an empty string. SO, I guess what I really want to know is how to set a default value for a listbox. PS. It also does not work if I set a value in the listbox properties window.
@@stephenhammond1745 Hi, I don't know why this isn't working for you. If I populate a listbox using either cells in the worksheet or using the AddItem method like this: Me.ListBox1.AddItem "Action" Me.ListBox1.AddItem "Adventure" Me.ListBox1.AddItem "Animation" I can set the value of the listbox to Adventure either like this: Me.ListBox1.Value = "Adventure" Or like this (the list items are indexed from 0): Me.ListBox1.Selected(1) = True Let me know what happens!
@@WiseOwlTutorials Ok, thanks. The first method works for 1 listbox but not the other. The only difference from what you've shown is that I didn't name the userform when setting the listbox.value. I've tried recreating and renaming both boxes with the same result. Very strange. If I ever figure it out I'll let you know. Thanks for your help.
@@WiseOwlTutorials Another piece of the puzzle. It seem that whatever listbox.value I try to set first in the code defaults to a "" value and the second defaults to a NULL value. If I reverse the order in the code, the same thing happens: first gets "" and second gets NULL. So, after populating the listboxes (which works fine by the way) the next 2 lines of code are: Me.listbox1.value = "test1" Me.listbox2.value = "test2". before this code executes, (hover cursor) Listbox1.value is "" Listbox2.value is NULL After the code executes listbox1.value is "" listbox2.value is test2 if I reverse the order in the code then listbox2 gets "" and listbox1 gets "test1".
Have downloaded the files, however when entering data into the userform, however the following code - If Len(SelectedGenres) > 0 Then SelectedGenres = Left(SelectedGenres, Len(SelectedGenres) - 1) that was added to allow users not to enter genres does not seem to work. Please Advise.
Awesome, did this and it works like a charm. Thanks for the excellent service and patience!!!. Another quick question please : i have deleted all the previous entries on the data sheet and when completing the userform i get the following error message Range("B2").End(xlDown).Offset(1, 0).Select
Have added another listbox named FilmGenres2 to the userform with a separate commandbutton to load data on another wsheet. But cant seem to get the data of the second listbox to load. Any suggestions?
Hi Andrew, thanks for all the help, i managed to sort this out. Another question please: I added another command button to the userform to delete items in the listbox. This is the code i am using and it removes the item from the listbox but not the sheet. Can you help me with a code that would remove the item from the listbox as well as the sheet? If Not FilmGenres.ListIndex = -1 Then FilmGenres.RemoveItem FilmGenres.ListIndex Else MsgBox "Select an entry" End If
Hi Andrew, this is the code i am using and it works well to find items. Thanks again for your help. However i have added another sheet to the data base (Lists2) and this only works when the sheet is activated, also still can't figure out to delete items from the listbox and related sheet. Any suggestions? Dim SearchRange As Range Dim FilmCell As Range Dim FilmName As String Dim FirstFilmCell As String FilmName =InputBox(“Type in a film name”) Set SearchRange = Range(“G3”, Range(“G2”).End(xlDown)) Set Filmcell = SearchRange.Find(What:=FilmName, MatchCase:=False, LookAt:=xlPart) If FilmCell Is Nothing Then MsgBox “No film was found” Else FirstFilmCell = FilmCell.Address Do MsgBox FilmCell.Value & “test” & FilmCell.Offset(0,1).Value Set FilmCell = SearchRange.FindNext(FilmCell) Loop while filmcell.Address FirstFilmCell End if End Sub
I've just figured out how to modify the back color back to white in the listbox :) Private Sub FilmGenres_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) If Not FilmGenres.BackColor = rgbWhite Then FilmGenres.BackColor = rgbWhite End If End Sub Hope it works
Rolls Royce vba education I recommend your channel to colleagues and clients all the time thank you.
Thanks Dave, much appreciated!
Hi Andrew! I just wanted to say that even 4 years after you uploaded this, I have found immense use out of this video and the previous ones. I wish I had found this series a month ago to be honest with you.
Thank you for such a clear, concise, and descriptive video!
Thanks again! Happy to hear that you're finding the videos useful!
I finally understand how the Listbox works after all these years. Thanks
My pleasure, thank you for watching!
Thanks for your video tutorials on VBA, very informative and helpful
You're very welcome, thanks for watching!
my final way of concatenating items from an array when using vba was:
SelectedGenres = SelectedGenres & iif(SelectedGenres ="", "",",") & FilmGenres.List(i)
this will prevent managing the trailing comma at the price of an extra comparison in when ok.
Nice solution!
My knowledge of lists boxes, especially multi-select list boxes was lacking.
I have a much better grasp on them now.
I'm familiar with current region, however I didn't know that you could click on a cell and use control + A to select the current region.
What a great tip for showing someone how a blank row or column can change the current region in a worksheet.
Thanks Andrew, you are an excellent teacher.
Another outstanding video series!
Thank you! Glad you enjoyed it and thanks for the support!
Hello Andrew, it's been great watching your videos, so much good insight to my vba journey.
I have a question I hope you can answer. I've added items into my listbox using the rowsource from my worksheet. It works perfectly fine and I'm happy with it but I want to be able to sort the list box (alphabetically or by date in a certain column) without sorting the the worksheet. I hope you can help help me out as I've tried many things but can't seem to get it right.
Hi! There's a nice article here which describes how to do this exceloffthegrid.com/sorting-listboxes-with-vba/
I hope it helps!
Hi Andrew - fantastic video as always. Appreciate it much. Could you kindly tell me if you have done any video on the list view control? I searched but couldn’t find. Many thanks in advance.
Hi Andrew, is there a way to insert into a listbox a select all item tickbox which would work in a way like e.g. as it works in Excel filters? I tried to make one utilizing the change event of the listbox but my code got complicated and ended in an infinite loop.
Hi Mark!
Yes, a bit tricky to make sure that changing the options programmatically doesn't trigger a cascade of the Change event! Here's some basic code for a form with a single list box called ListBox1
Option Explicit
Private ChangeEventDisabled As Boolean
Private Sub UserForm_Initialize()
With Me.ListBox1
.AddItem "Select all"
.AddItem "A"
.AddItem "B"
.AddItem "C"
End With
End Sub
Private Sub ListBox1_Change()
Dim i As Long
'Exit sub if events are disabled
If ChangeEventDisabled Then Exit Sub
'Check if Select All option has been changed
If Me.ListBox1.ListIndex = 0 Then
'Prevent subsequent changes from triggering this event
ChangeEventDisabled = True
'Loop through Listbox items except for Select All option
For i = 1 To ListBox1.ListCount - 1
'Set Listbox item to same status as Select All option
Me.ListBox1.Selected(i) = Me.ListBox1.Selected(0)
Next i
End If
'Re-enable change event
ChangeEventDisabled = False
End Sub
@@WiseOwlTutorials Brilliant, thanks Andrew, it is working on my computer. I like the way how you write codes, it is so neat and elegant :-)
@@markbocsor3704 Excellent! Happy to hear that it helped!
Nice tutorial, plz make video show data from MS access , more than 10 column with headers
Hello Andrew, thanks a lot for your priceless videos, I am having difficulty with finding something useful about listview control, any suggestions?
Hi - I really like the way you explain things. I wonder if you have any ideas about a problem I am trying to solve.
Its about being able to manipulate background color of list row using vba.
Is it possible to program several background color per line in listboxes? In my userforms I have listboxes and I get their input from named ranges - which I populate via queries. so each row of a list box contain multiple cells from that named range. Is there a way to program the background coloring of cells so that the listbox would show them? currently I paint the background of cells in named range with vba and when I navigate to it or when I export that named range to a different workbook - I can see that color background worked fine,
But when I look at same named range thru the listbox - the background colors do not show. Any ideas on how to overcome this problem?
The application I am building will allow the User to set up new lists that are not yet populated. For Example, I will create a Table for a Named List called 'Cost Codes'.
Initially the Cost Codes List will be unpopulated (will have one blank row); waiting for the User to start inputting their own Cost Codes with Descriptions.
With the .End(xlDown) method, I find that the Code fails, however; if I have the one Row with data in it, then the Sub runs fine.
I am a very rank amateur to Excel VBA, hence why I am very appreciative of your Website & UA-cam content! Thank you!
Will it be necessary to write a Sub that will have the end User initiate the Table, Name the Range, and then enter their Cost Codes...
I hope I have explained this well enough to be useful for you.
You input is greatly appreciated!
Hi there, it was beautifully explained! Using the End property with an unpopulated list is a common way to experience run time errors! So much so that I made a separate video on various ways to find the last cell which you might find useful ua-cam.com/video/pI2px2KoapU/v-deo.html
I hope it helps!
Hi Andrew! The functions part of VBA is a bit confusing;
I have gone through the introduction part in one of your videos on function. Need your suggestion on how to go about..thank you in advance!!!
+WiseOwlTutorials Thank you Andrew! Do you have a series on functions? Just like UserForms?
Great series, thank you.
Very nice tutorial..
Lot loves from India.
I learnt many tricks and tips from your channel...❤❤
I have one doubt
In the vba list box I have few items in that I want to highlight one particular item with red or some other colour.
Could please help me out in this
Hi! No sorry, you can't do that with a listbox. You could use a ListView control instead although it's more effort to use!
Hi Andrew, hi all,
Your videos are great as always, thank you Andrew!
When watching at this video, in the "if" you write at min 12:30, I was wondering how to avoid the empty case. So I thought by writing the following code in the "if", it might be another way to make it :
If SelectedGenres = "" Then
SelectedGenres = FilmGenres.List(i)
Else
SelectedGenres = SelectedGenres & "," & FilmGenres.List(i)
End If
... or would this be longer ?
In any case, thank you very much for your clear explanations, examples, and vast knowledge you give in the videos.
Andrew was wondering if this could be used - add a textbox which captures the genres selected by the user and write a validation code for that?
+WiseOwlTutorials Fantastic! Cheers :)
Anytime I use the scroll bar to scroll down my ListBox, and then try to select an item, it automatically scrolls back to the top and does not select the item. I have created a ListBox where I have over 350 items, so I also added a search function, and when you select an item, you have to click a command button which moves it to a TextBox, creating my end-result list. I think this is similar issue as is with the scroll bar function in other videos, but I cant seem to find any information to verify this. Also is there a way where I can say “If ‘in listbox’ “XYZ” is selected and added to textbox..” no other values can be added? Maybe disabling the Add Selected Item button through an if statement. Any help would be greatly appreciated, and as this is a work related project, the speedier the better.
Thanks
Hey, I really appreciate your service. I need your assistance on listbox columns. I want to format a specific column.: font...alignmnet, color, size
And how exactly do you do this without having the list inside a frame?????
I inserted an List box (ActiveX control) but it always resizes when I am closing and opening the file. How can I prevent that?
Outstanding, thank you sir !
Hi I have a doubt .Like if we have both text and some cell with borders looks like image.so we need to select it as range and paste it in listbox .Is that possible ?
Hi! No, you can't put an image in a listbox as far as I know.
Thanks For This Series But I Have A Question. I Want The User To Select A Genre (Like You). When They Don't Select Anything And Click 'Add To List' Button, It Turns Pink Successfully, Indicating That It Must Have Some Selection. But When I Select Something The ListBox Is Still Pink. Is There Any Way That After Selecting Even One Item, The List Box Turns White Again. (Which Event Of The ListBox I May Use?)
Thanks In Advance
+WiseOwlTutorials Thanks. Happy Shakespeare's Day
Thank You!
Hi Andrew, is it also possible to write the genres not in one cell but below each other? I know that is has nothing to do for your video.
But iam googled a lot and i didnt find it. So why not asking the VBA Guru ;-)
I tried: SelectedGenres = SelectedGenres & FilmGenres.List(i) & VBNewLine
SelectedGenres = Left(SelectedGenres, Len(SelectedGenres) -1) 'To clear the last VBNewLine.
In my msgbox everything is filled in nicely (below each other) but when i write my code:
wsTest.Range("A1").Value = SelectedGenres
Everything is filled in in the same cell. Its like when i press ALT+Enter when writing in my cell. :( :(
Is there a solution? Thank you so much for al the other video's as well!! Now you made want to buy Excel 2016 :o ;-)
This worked for me.
The references do not have anything to the with film generes, because they are from one of my own projects, but the idea behinded should work just fine. Therefore i = 16 is just the row that I would like my listbox input to start being inserted form.
Insert a private sub insted of a private function, and call it in the sub AddToList_Click.
Private Sub ChooseProduct()
Dim LoopCounter As Integer
For LoopCounter = LBound(ListBox1.List) To UBound(ListBox1.List)
If ListBox1.Selected(LoopCounter) Then
i = 16
While Cells(i, 1) ""
i = i + 1
Wend
Cells(i, 1) = ListBox1.List(LoopCounter)
End If
Next LoopCounter
End Sub
Anybody that could please let me know if there is a way to limit the number of items a listbox can contain?.I mean I´m triying to have a listbox with only two items no more than them so I would like to code a constraint.
Finally doing some coding with Userforms but having some trouble with listboxes. I have 2 listboxes on the form. I load the boxes with values from a data list on a worksheet. My trouble is that once I initialize the form and add values to the lists, one of the list boxes show a listbox.value of NULL. The other shows a listbox.value of "". They also behave differently when I try to set a default value in each (one accepts the default value, the other does not) but I can't see any difference in the way they are configured. I've tried deleting/recreating, with the same result. Any ideas?
Hi! I'd need to know more about how you're populating the listboxes and trying to retrieve the value!
I populate the listboxes in the userform_initialize event from values in a worksheet using a for-each loop. Works fine. After the loop I then try to set both listbox.value properties to values from the same worksheet range.(I do this so if the user does not select a value then the listbox.value won't be an empty string). If I run the code and hover over the listbox.value properties before I try to set them, but after the listsboxes have been populated, one of the listbox.values is set to "NULL", the other is set to an empty string"". After I try to set the values, the one that started out as "NULL" accepts the value, the other does not but still shows an empty string. SO, I guess what I really want to know is how to set a default value for a listbox. PS. It also does not work if I set a value in the listbox properties window.
@@stephenhammond1745 Hi, I don't know why this isn't working for you. If I populate a listbox using either cells in the worksheet or using the AddItem method like this:
Me.ListBox1.AddItem "Action"
Me.ListBox1.AddItem "Adventure"
Me.ListBox1.AddItem "Animation"
I can set the value of the listbox to Adventure either like this:
Me.ListBox1.Value = "Adventure"
Or like this (the list items are indexed from 0):
Me.ListBox1.Selected(1) = True
Let me know what happens!
@@WiseOwlTutorials Ok, thanks. The first method works for 1 listbox but not the other. The only difference from what you've shown is that I didn't name the userform when setting the listbox.value. I've tried recreating and renaming both boxes with the same result. Very strange. If I ever figure it out I'll let you know. Thanks for your help.
@@WiseOwlTutorials Another piece of the puzzle. It seem that whatever listbox.value I try to set first in the code defaults to a "" value and the second defaults to a NULL value. If I reverse the order in the code, the same thing happens: first gets "" and second gets NULL.
So, after populating the listboxes (which works fine by the way) the next 2 lines of code are:
Me.listbox1.value = "test1"
Me.listbox2.value = "test2".
before this code executes, (hover cursor)
Listbox1.value is ""
Listbox2.value is NULL
After the code executes
listbox1.value is ""
listbox2.value is test2
if I reverse the order in the code then listbox2 gets "" and listbox1 gets "test1".
Have downloaded the files, however when entering data into the userform, however the following code - If Len(SelectedGenres) > 0 Then
SelectedGenres = Left(SelectedGenres, Len(SelectedGenres) - 1) that was added to allow users not to enter genres does not seem to work. Please Advise.
Hi there, thanks for your prompt reply. I have downloaded this again and still no luck. Using excel 2016, can this be the problem?
No error message. I would like to enter data without selecting the genres list box. When i try this it highlights in pink.
Any other suggestions ?
Sorry i didn't see your previous response. Just so i am clear: i would like to add all detail bar the film genre to the sheet. Is this possible?
Awesome, did this and it works like a charm. Thanks for the excellent service and patience!!!. Another quick question please : i have deleted all the previous entries on the data sheet and when completing the userform i get the following error message Range("B2").End(xlDown).Offset(1, 0).Select
Have added another listbox named FilmGenres2 to the userform with a separate commandbutton to load data on another wsheet. But cant seem to get the data of the second listbox to load. Any suggestions?
Hi Andrew, thanks for all the help, i managed to sort this out. Another question please: I added another command button to the userform to delete items in the listbox. This is the code i am using and it removes the item from the listbox but not the sheet. Can you help me with a code that would remove the item from the listbox as well as the sheet?
If Not FilmGenres.ListIndex = -1 Then
FilmGenres.RemoveItem FilmGenres.ListIndex
Else
MsgBox "Select an entry"
End If
Thanks for all the help, have tried without success. Will play around some more. Once again thanks!
Hi Andrew, this is the code i am using and it works well to find items. Thanks again for your help.
However i have added another sheet to the data base (Lists2) and this only works when the sheet is activated, also still can't figure out to delete items from the listbox and related sheet. Any suggestions?
Dim SearchRange As Range
Dim FilmCell As Range
Dim FilmName As String
Dim FirstFilmCell As String
FilmName =InputBox(“Type in a film name”)
Set SearchRange = Range(“G3”, Range(“G2”).End(xlDown))
Set Filmcell = SearchRange.Find(What:=FilmName, MatchCase:=False, LookAt:=xlPart)
If FilmCell Is Nothing Then
MsgBox “No film was found”
Else
FirstFilmCell = FilmCell.Address
Do
MsgBox FilmCell.Value & “test” & FilmCell.Offset(0,1).Value
Set FilmCell = SearchRange.FindNext(FilmCell)
Loop while filmcell.Address FirstFilmCell
End if
End Sub
Hi Andrew Active sheet sorted:). However i can still do with some guidance as regarding deleting of item.
ListBox enter event will change backcolor from pink to white.
i want show header in listbox
I've just figured out how to modify the back color back to white in the listbox :)
Private Sub FilmGenres_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If Not FilmGenres.BackColor = rgbWhite Then
FilmGenres.BackColor = rgbWhite
End If
End Sub
Hope it works
Good stuff!