Combo Boxes

[TheRealDave]

I have a combo box in a userform linked to a rowsource. I also have a macro in the userform. What I want to happen is when the combo box has selected an item and the button is clicked I want the item selected to be deleted and the cell to the right of it too.

For example..

Rowsource
A1
A2
A3
A4
A5

In the combo box I picked cell A3. When I press the button I want the content of cell A3 and B3 deleted.

[sweet_dreams]

Hi, TheRealDave

Paste this code to your Button Click event:

Dim address As Range
Set address = Range(ComboBox1.Value)
address.Select
address.ClearContents
address.Offset(0, 1).Select
Selection.ClearContents

hope this help

regards,
sweet_dreams

[sweet_dreams]

i optimalized my previous code now looks better

Range(ComboBox1.Value).Select
Range(ActiveCell, ActiveCell.Offset(0, 1)).ClearContents

regards,
sweet_dreams

[TheRealDave]

You make it seem so easy! That's a great help, thanks!

[sweet_dreams]

i suggest you use this second code. and don't forget to tick thread as resolved (if of course it is resolved )

regards,
sweet_dreams

[TheRealDave]

will do. off to try it now

[TheRealDave]

ahh damn.. marked it resolved when it isnt.

I get "run time error '1004':

Method 'Range' of object '_Global' failed."

[sweet_dreams]

hm, on my PC this code works perfect:

Range(ComboBox1.Value).Select
Range(ActiveCell, ActiveCell.Offset(0, 1)).ClearContents

make sure that values of cell addresses in your RowSource property are correct

s_d

[TheRealDave]

I have the control source set to Sheet!A1 and the rowsource to sheet!List

where the list selects column A

any problems?

[sweet_dreams]

are you using any other code in your macro??? can you paste the whole code you are using???
oh and have you changed the name of your combobox in the code???

s_d

[TheRealDave]

that's the only code in there. It's very basic. I have different pages in a userform with the combo box and button on each page. I think the problem is selecting the cell somewhere but I just cant figure it out

[sweet_dreams]

the code which I gave you is selectiond cell with address which was choosen in combobox: so if you clicked in your combobox A1 value, code selects A1 cell and then selects range A1:B1 and clear it's content.

well, I don't have the foggiest idea what's wrong.

[TheRealDave]

Exactly what I was thinking. HOWEVER () this is for a school project and I can use this as part of my evaluation (if that's ok with you) stating that the code I used should have worked and I could then evaluate it to hell and write all this fancy stuff etc

Still, I dont see why what you gave me shouldn't have worked. All the names, cells etc are all correct

Thanks anyways

[sweet_dreams]

you don't need to set control source property, row source is enought if you only want to assign list of values to your combobox

hm I don't understand wha you mean by saying:

where the list selects column A

try row source property as for eg sheet1!a1-a11

[TheRealDave]

what i meant by that is the list was the entire column A. This was because there was to be an unlimited ammount in that column and it had be selected within the system. I just tried removing the control source and am still getting the error. It's ok though, should be alright for me to just do it as it is.

[sweet_dreams]

how did you create this list ??? I think here could be a problem

[TheRealDave]

Went to the sheet. Pressed A to select the whole column (which works fine in other places) and named it using the name box. Like I said, it's used as i list in validation on another page so there's no error in the list itself as far as I can tell.

[sweet_dreams]

where do you exactly get the error message ???

[TheRealDave]

Isn't that. I can still view the list. The correct property I have for that is "PERIPHERALS!PSR" where the sheet name is PERIPHERALS and the list is PSR.Not sure if this one's going to be solved

[sweet_dreams]

maybe attach your excel file so I can take a look at it in the morning I'm too tired to do this now.

regards,
sweet_dreams

[TheRealDave]

That will be a problem as i don't have it with me. It's on a different computer that i have remote access to and no way of moving it, sorry. I think i'm just going to leave it and see how it goes..