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

 

[LostCause]

I put the following into a Button

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

When I click it, nothing happens.

I can't use a listbox because some of the values will return results which I don't want to be able to navigate to, so I was planning on writing another macro to hide the entire row (along with that row's button), so they need to be able to be clicked individually.

 

[Norie]

How did you 'put' it in a button?

 

[LostCause]

I drew a button from the forms menu.

I was prompted to assign a macro to it. I named the macro and pasted in your line of code

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

Edit: It's working now. I don't know how, I haven't changed anything?

Wow I'm sorry man, thanks for your help!

 

[erik.van.geit]

Hi,

the code assumes you have three buttons where the names are like
Button 1
Button 2
Button 3
or
CommandButton 1
CommandButton 2
CommandButton 3

important is that the names end with the numbers 1, 2, 3

assign all buttons to the same macro
application.caller will get the name of the button
the macro will "read" the right character Right(application.caller, 1) to perform some operations ...

I can't use a listbox because some of the values will return results which I don't want to be able to navigate to

this could be solved, still using a listbox: just filter the list before you fill it

kind regards,
Erik

 

[LostCause]

Thank you very much for your explanation.

I modified the code to give me the following three macros which seem to work well and are less complicated (and function without having corresponding button numbers)

Sub first()
Range("C15") = Range("C6").Offset(0, 0)
End Sub
Sub second()
Range("C15") = Range("C6").Offset(1, 0)
End Sub
Sub third()
Range("C15") = Range("C6").Offset(2, 0)
End Sub

Does anyone see a flaw with that?

Thanks

 

[erik.van.geit]

if you are anyway going for three macros, you don't need the offset
Range("C6")
Range("C7")
Range("C8")
would do


thinking about this: three optionbuttons would be more adequate to my sense
link optionbuttons to cell A1 (or another)
formula in C15: =INDEX(C6:C8;A1)

(seems like I can't "convert" you to the filtered listbox)

 

[LostCause]

Thank you.

I knew it was something simple, I'm just not cut out at this computer programming stuff!

It's not that you can't convert me, I'll follow the advice of the experts! It's just my boss wants everything to be consistent (we've used buttons throughout the tool), so a listbox just looks out of place.

Out of interest, how would the filtered listbox work?

 

[erik.van.geit]

Out of interest, how would the filtered listbox work?

depends of the criteria ...
here an example
http://www.mrexcel.com/board2/viewtopic.php?t=204743

 

[LostCause]

Thanks I'll check it out