Functions of a Listbox without using a Listbox!?

[LostCause]

Firstly, thanks for reading.

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

 

[sykes]

Hi Lost!

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

 

[LostCause]

Each return to C15 Thanks!

 

[Norie]

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.

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.

Sub Macro1()
    Range("C15") = Range("C6").Offset(Right(Application.Caller, 1))
End Sub

 

[sykes]

ADVERTISEMENT

........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]

Thanks for your responses guys.

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.

I hope this made sense

Thanks!

 

[LostCause]

ADVERTISEMENT

........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.

The values please

Thanks

 

[Norie]

Did you try the code I posted?

 

[LostCause]

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

 

[Norie]

How did you try it?

How didn't it work?

PS what's the problem with using a Listbox?