VBA button - reference to another sheet

[Baoli]

Hi,
Sorry if this question is abit to "simple" to answor, but im extremly new at this - so if someone has time to help i would be very thankful!

OBS :Code below.
What i want to do is to press a button that randomly chooses a "restaurang" from a list (thats on another sheet) and puts it in a cell (on the current sheet). All in the same excel file, just diffrent sheets. So in the sheet with a list of "restaurangs" theres a.. list which it should choose from.

Below is the code. And under i explain what the problem is.

Please Login or Register  to view this content.

I believe that, since i didnt make the code. that the "set rng" doesnt understand that i want to set the range in another sheet since now it randomly picks a cell between b3 and b20 in the CURRENT sheet and puts it in B11, instead of picking the ones from the OTHER sheet.

Any ideas :/ ? Would be extremly greatful!

[arlu1201]

You just need to change this row

Please Login or Register  to view this content.

Since i do not have a sample file to look at, all you need to do is change the "Radata-Restaurang" to the name of the "OTHER" sheet.

[Baoli]

It already refers to the other sheet. "Rådata-Restaurang" is the other sheet. "Val" is the current sheet.

[arlu1201]

This is what is messing up the code -

Please Login or Register  to view this content.

You need to find the address of the cell that contains the random number and then offset it by 1 column.

[Baoli]

=(...
I dont get it. So sorry mates. And thanks so much for the help.
But what i need is. The button is on the same sheet as i want to randomly get a name to appear (Cell B5 in sheet "Val"). But the LIST is in (("Rådata-Restaurang").Range("B3:B20"))
So how do i change these 2 last lines to get it to work? =S

Please help.

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

Regards

[Baoli]

EF showroom.xlsm

Here is the file (very deletetd from information) but the "button" and code is still there

Hope it may help,
I would be EXTREMLY greatful!

Thanks,
Baoli

[dangelor]

Possibly...

Please Login or Register  to view this content.

[Baoli]

Works perfectly sir! Thank you!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.