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
 

Some videos you may like

Excel Facts

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

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,755
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
75,831
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,755
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
75,831
Office Version
  1. 365
Platform
  1. Windows
Did you try the code I posted?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
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?
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top