VBA command works in Userform but not in Worksheet

**neurosis737** · 03-01-2011, 07:21 AM

I've wasted the last couple of hours trying to figure this out. I want to get the text of the selected value in a Form Controls listbox which is in a worksheet. In Excel 2003 I could just type:

Sheets(1).Range("A1").Value = myListBox.Text

The best I've been able to do in Excel 2007 is to get the index and that uses

Sheets(1).Range("A1").Value = Sheets(1).ListBoxes(1).ListIndex

The command works fine if I put the listbox in a userform instead, but I need to have it on a worksheet. I also don't understand why I have to use ListBoxes instead of whatever I name the listbox. Has MS changed something in 2007 or have I just done something wrong?

**Colin Legg** · 03-01-2011, 07:26 AM

Hi,

It's because you've inserted a "forms" listbox onto the worksheet rather than an "activeX" listbox. When you go to the ribbon to insert the control, you'll see that there are two banks of controls: "forms" and "activeX".

**royUK** · 03-01-2011, 07:36 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**neurosis737** · 03-01-2011, 07:37 AM

Worked like a charm. Thanks!!! Still, wasn't it possible to use the regular form controls in previous versions of excel?

**Colin Legg** · 03-01-2011, 07:39 AM

Hi,

Worked like a charm. Thanks!!! Still, wasn't it possible to use the regular form controls in previous versions of excel?

Please add code tags around your code in post #1 and then I will edit this reply to answer this question.

VBA command works in Userform but not in Worksheet

LinkBack

Thread Tools

Rate This Thread

Display

VBA command works in Userform but not in Worksheet

Re: VBA command works in Userform but not in Worksheet

Re: VBA command works in Userform but not in Worksheet

Re: VBA command works in Userform but not in Worksheet

Re: VBA command works in Userform but not in Worksheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions