# Functions of a Listbox without using a Listbox!?

#### LostCause

##### Board Regular

I was previously using a listbox to do this but have been ordered not to use one!

What I would like to happen is, have a macro for each button that changes the value in cell C15, depending on which button was pressed (so Button 1 returns A, Button 2 returns B and Button 3 returns C.)

Just one example will do, I can adapt it to the other buttons!

Many thanks

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### sykes

##### Well-known Member
Hi Lost!

Question:
Do you want "A" "B" & "C" respectively returned to C15, or the value of C6,7,and 8 respectively?

#### Norie

##### Well-known Member
You could do this in one macro, provided the buttons come from the Forms toolbar.

For example you could assign this macro to each button.
Code:
``````Sub Macro1()
Range("C15") = Chr(Right(Application.Caller, 1) + Asc("A") - 1)
End Sub``````
Now this does exactly what you asked for, puts A, B or C in C15.

But I've a feeling you might actually want something a little different, for example pulling the values from C6:C9.

If that's the case try this.
Code:
``````Sub Macro1()
Range("C15") = Range("C6").Offset(Right(Application.Caller, 1))
End Sub``````

#### sykes

##### Well-known Member

........yes, but do you want the LETTERS A B and C, or will the value of C6, 7 or 8 change, in which case you want the VALUES of C6,7 or 8 in C15?

#### LostCause

##### Board Regular

Maybe I oversimplified it a bit too much:

The letters in C6:C8 will vary depending on other things in the spreadsheet. Let's say they are product codes.

The purpose of the three buttons is to update cell C15 with the product code in question. I was then going to make the rest of the macro go to another sheet where the cell is referred and a lookup will be performed on it, to display information.

So Button 2 enters B in cell C15, and goes to another spreadsheet where more information about item B is displayed. I have no problem with the navigation to another sheet, it's the getting data into a specific cell part I'm struggling with.

Thanks!

#### LostCause

##### Board Regular

........yes, but do you want the LETTERS A B and C, or will the value of C6, 7 or 8 change, in which case you want the VALUES of C6,7 or 8 in C15?
Oh sorry, I should learn to read.

Thanks

#### Norie

##### Well-known Member
Did you try the code I posted?

#### LostCause

##### Board Regular
Yes... I tried the second one but couldn't get it to work.

#### Norie

##### Well-known Member
How did you try it?

How didn't it work?

PS what's the problem with using a Listbox?

Replies
2
Views
52
Replies
3
Views
34
Replies
3
Views
78
Replies
3
Views
84
Replies
8
Views
144

1,109,444
Messages
5,528,799
Members
409,835
Latest member
Mafu1267

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...