Button macro questions

[Baoli]

Hi!
Im EXTREMLY newbie at this so let me try and explain what im trying to do:

Thing nr 1:

• Where i work there are alot of places to eat.
• I have created a list of all places
• I want to do a button that, when i press it, it chooses 1 of the places randomly (the name of the place) and puts that in a cell.

What i have done:
I have made a =index function (in that cell) that randomly picks one in index, and a button that refreshes -> which randomly picks a place (works). However, due to other functions in my worksheet i can not use this "refresh" function since it ruins the rest.

What i want:
A button that checks the "index" (list of places) - randomly selects one, and puts that name in a cell (without any refresh function). In other words: Nothing in the cell where i want the place to pop up, but with a button that does the "randomselect" thing for me and puts one in the cell.
PS: The Index of places is in another sheet and the range is from b3:b40

Thing nr 2:

I have created another button (copy paste from a website) that selects a persons email, writes the subject, etc. However: I want it also to select a couple of cells from another sheet and put the content of it in the body.

Ill try to show the script first. Hope i can =/

Please Login or Register  to view this content.

Now where it sais ".Body = (rng)" i simply what that "(rng)" to be a certain sheet between a "A2 and H16". and then to use that contant (within those cells) as text.

-------------------

Please notice that im EXTREMLY new at this, so i wont understand much of text. I just wonder if any kind soul can help with these 2 buttons.

Kind regards,
Doite

[Baoli]

anyone? =(

[Baoli]

Ah! I solved the email question, that is "thing 2". Now all i wonder is the random cell index thing

[tkowal]

RandomSelectionFromList.xlsm

This may help get started ..... Be sure to enable macros and the code is in Module1


The example will randomly choose from a list a places (defined as a range -- "MyList") and place the result in cell "A1" each time you press the button.

[Baoli]

Thx mate =) Ill try it out if i ever get some more spare time away from my original duties.

[Baoli]

Mate!
I tried
Worked on yours but when i ... remade it alittle it doesnt

Sub RandomPlace_Click()
Dim ListCount As Integer, num
Dim RandomNumber As Integer
Dim rng As Range

Set rng = Sheets("Rådata-Restaurang").Range("B3:B20")
ListCount = rng.Count
RandomNumber = Int(ListCount * Rnd + 1)
ActiveSheet.Range("A1").Select
ActiveSheet.Range("A1").Value = ActiveCell.Offset(RandomNumber - 1, 0).Value
End Sub

What i changed was the rng. from activesheet.range("mylist") to sheets("blablabal").range("a2:b20")

It doesnt give me an error, but nothing happends. It just selects "A1" in the sheet where i have the button, but doesnt place anything there

[Baoli]

Now it starts picking stuff in the sheet where the button is

I redid the code so its:

Sub RandomPlace_Click()
Dim ListCount As Integer, num
Dim RandomNumber As Integer
Dim rng As Range

Set rng = Sheets("Rådata - Restaurang").Range("B3:B20")
ListCount = rng.Count
RandomNumber = Int(ListCount * Rnd + 1)
ActiveSheet.Range("B11").Select
ActiveSheet.Range("B11").Value = ActiveCell.Offset(RandomNumber - 1, 0).Value
End Sub

But as u se, i want to go to "sheets("rådata - restaurang") first, then range ("blabal").
Not the range in this sheet

Any fast fix?

[Baoli]

anyone =( Sorry i ask, but.. i cant figure it out.

[Mordred]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[Baoli]

SOLVED - see post "[SOLVED] VBA button - reference to another sheet "