Functions of a Listbox without using a Listbox!?

LostCause

Board Regular
Joined
Jul 13, 2006
Messages
99
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows

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

Board Regular
Joined
Jul 13, 2006
Messages
99
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

Board Regular
Joined
Jul 13, 2006
Messages
99

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
Did you try the code I posted?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
How did you try it?

How didn't it work?

PS what's the problem with using a Listbox?
 

Forum statistics

Threads
1,141,137
Messages
5,704,490
Members
421,353
Latest member
jekoxien15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top