Solved: write a VBA program for me.

[ryrobinson7]

I was wondering if someone that knows VBA in execel could creat a VBA program for me. This program will create a mini-poster. The poster will contain cells with backgrounds in different colors, with another color name printed inside the cell. Hear are some spacifics.

1. It should be a square of 4 to 16 cells (four accross, four down) The number should be determined by the user. (Input box or somthing).
2. The program should produce a random number from 1 to 8.
3. The random number should be used to choose a color and word from an array. This controls what color the cell becomes and what word is inserted.
4. Although random, no color should be used in more than 10% of the squares.
5. The cells should be surrounded by wider than normal borders (how wide is up to you) and the font should be set to 14.

[jindon]

4. Although random, no color should be used in more than 10% of the squares.

Impossible when user enter the size of 9+.....

[ryrobinson7]

That's true. strike number 4 then. Just do 1,2,3, and 5. I also noticed I made a typo in number 3. It should be: The random number should be used to choose a color and an opposite color word from an array. This controls what color the cell becomes and what word color is inserted.

The attached file is what it should kinda look like. I want the words to be in the exact center of the box though not at the bottom.

Thanks alot.

[ryrobinson7]

!.) Hear is the process that I am looking at. Have a click button that will ask for the size of the of the sqares. minimum of 4 squares no greater than 16 squares.
2.) There should be 8 colors and 8 color names that should be randomly choosen from an array and put into the boxes.
3.) The cells should be surrounded by wider than normal borders and the font of the color names should be 14 and centered in the middle.

I thank who ever works on this very much.
Feel free to ask me any other questions.

[jindon]

Well I'm just curious about your word "opposite"

How do you define "opposite", since I cannot figure it out from your file.

[ryrobinson7]

Example, if the random number 1-8 chooses a blue square to stick in A1, then the word that appears in the blue square is going to be green, red, orange, or another color name other than blue.

[jindon]

So it is not "opposite", but random choice of word other than its colour.

Is that what you are saying?

Anyway, have you tried anything for yourself?

if any, post it here..

[TonyJollans]

Hi ryrobinson7,

Welcome to VBAX!

What I try to do, and what most other people here and on other online forums do, is to help people to help themselves. If you have tried to do something and have a particular problem please post it and I will happily look at it. I don't, however, do work for free and have just skimmed the thread and have not looked at your file. I think you will find most other people have much the same attitude and you will be more likely to get help if you make an effort yourself.

[jindon]

Agree!

I have already done the code, but I will not post it until you show us some effort.

sorry

[ryrobinson7]

Yes that is right. Sorry for using the wrong word.

Ok I do have some, but not much. I am having problems with the arrays and stuff. I have it down to were I click a button and it changes the boarder size to thicker than normal, the font set to 14, and an input box comes up asking for how many boxes.

What I need it to do next is the program to take the info from the inputbox and apply it to the other information. I need the program to produce a random number from 1-8 and the random number should be used to choose a color and word from an array. It will control what color the cell becomes and what word is inserted. I am also not shure on how to center the word in the exact middle of the box.

I do thank you for helping me on this jindon.

[jindon]

OK
here's just a sample for you
[vba]
Sub test()
Dim x, rng As Range, r As Range
Dim myList
x = Application.InputBox("Enter size from 4 to 16", Type:=1)
Set rng = Range("a1").Resize(x, x)
myList = [{1,2,3,4,5,6,7,8;6,11,3,10,13,16,38,53;"Brown","Pink","Grey","Purple","Gree n","Red","Blue","Yellow"}]
rng.CurrentRegion.Clear
Randomize
For Each r In rng
x = Int((8 * Rnd) + 1)
With Application.WorksheetFunction
r.Interior.ColorIndex = .HLookup(x, myList, 2, False)
r.Value = .HLookup(x, myList, 3, False)
End With
Next
With rng
.ColumnWidth = 10
.RowHeight = 50
With .Font
.Size = 12
.Color = vbWhite
.Bold = True
End With
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.Weight = xlThick
End With
End Sub[/vba]
p.s.

I'll go off-line now and will be busy tomorrow.
Good Luck

[ryrobinson7]

Thanks, I made a couple revisions to it. I made it a 2 by 2, 3 by 3, and 4 by 4 squares. I also edited a couple other things.

I am wondering if we can add a couple other things to it. I was wondering if we could add a rectangle under the button that will display the box color that you click on. Say I click on cell A1 and it is yellow, but says brown, it will display the word yellow in the rectangle under the button. then if you click on another cell it will do the same thing and tell you the box color not the word color.

I was also wondering if we could make it, so that 1 color won't appear in more than 25% of the box.

[jindon]

Don' just be wondering. Try it for yourself.

Remember, those kind of alteration due to the change of your own cost you more than you ever imagine in the real world..

That is why I asked some questions before I wrote the code...

[ryrobinson7]

ok. I have been working on it and hear is what I have so far. I know the code Range().Select is when you are selecting a cell, so I need that in there eventually to make it so that when I select a cell it will display the name of the color. Right now I can not get it to work with nameing the right color. It always names the last color in the square and it is only naming the name of the color not the acctuall color.

[tpoynton]

What's the purpose of this program? Sounds like a homework assignment...

[jindon]

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/2/2006 by Ryan Robinson
'
'
    Range("B1").Select
End Sub

Is this what you call "Done something?"

[ryrobinson7]

No. Double click on sheet1. That was just a recorded macro that I did and forgot to erase. Below is what I did. I added info to InputBox, I have added Range("A7").Value = r.Value. I also did some stuff on there with merging cells and editing cells. I am having problems with the Range("A7").Value = r.Value. I am only getting the last cells color text info. I know I am getting that, becuse I possibly have to add a Range().Select coding and r.Value is the color text value not the acctual color. I tried using Range("A7").Value = r.Interior.ColorIndex, but all it gave me was numbers not text. Example of what I want to accomplish: When I click on a cell that has the color pink in it, it is supposed to display it in cell A7 in text form "Pink". I have tried other ways of inputing the info and it only gives me numbers. I have truly worked on it, for hours acctually, and if you want to belive me or not that is up to you. I thank you for helping me with all the other code, but I am not very good at VBA and was just wanting some assistence with it.

[vba]
Private Sub CommandButton1_Click()

Dim x, rng As Range, r As Range
Dim myList

x = Application.InputBox("Enter size of square: 2=2 by 2, 3=3 by 3, or 4=4 by 4", Type:=1)
Set rng = Range("a1").Resize(x, x)
myList = [{1,2,3,4,5,6,7,8;6,11,3,10,13,16,38,53;_
"Brown","Pink","Grey","Purple","Green","Red","Blue","Yellow"}]
rng.CurrentRegion.Clear
Randomize



For Each r In rng
x = Int((8 * Rnd) + 1)
With Application.WorksheetFunction
r.Interior.ColorIndex = .HLookup(x, myList, 2, False)
r.Value = .HLookup(x, myList, 3, False)
Range("A7").Value = r.Value
End With

Next

With rng
.ColumnWidth = 10
.RowHeight = 50

With .Font
.Size = 14
.Color = vbWhite
.Bold = False
End With

.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.Weight = xlThick
End With

End Sub
[/vba]

[jindon]

Range("A7").Value = r.Value

What are you tring to do with this line?

And what about all the other issues you raised in the previous post?

[ryrobinson7]

I am trying to have A7 display the color of a selected cell. I am not shure how to put that into code. Whould I have to do somthing like this:

Dim C1 As Integer

a = Range("A1").Select = r.Interior.ColorIndex

Range("A7").Value = a

I would add one for each cell A14, but I don't think a = Range("A1").Select = r.Interior.ColorIndex is possible? But also with the r.Interior.ColorIndex it gives me the number representing the color of the box instead of giving me the name. I don't know how to set that to work eather.

[jindon]

You are not answering to the question from tpoynton and mine correctly.

Have you tried the other issues that you have raised???

What you did is almost nothing to me.