PDA

View Full Version : Solved: write a VBA program for me.



ryrobinson7
04-29-2006, 04:51 PM
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
04-29-2006, 10:08 PM
4. Although random, no color should be used in more than 10% of the squares.

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

ryrobinson7
04-29-2006, 11:06 PM
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
04-29-2006, 11:17 PM
!.) 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
04-29-2006, 11:25 PM
Well I'm just curious about your word "opposite"

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

ryrobinson7
04-29-2006, 11:54 PM
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
04-30-2006, 12:01 AM
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
04-30-2006, 12:31 AM
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
04-30-2006, 12:36 AM
Agree!

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

sorry

ryrobinson7
04-30-2006, 12:42 AM
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
04-30-2006, 12:47 AM
OK
here's just a sample for you

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","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)
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
p.s.

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

ryrobinson7
04-30-2006, 01:00 AM
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
05-01-2006, 06:26 PM
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
05-02-2006, 11:06 AM
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
05-02-2006, 11:23 AM
What's the purpose of this program? Sounds like a homework assignment...

jindon
05-02-2006, 06:14 PM
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
05-02-2006, 08:02 PM
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.


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

jindon
05-02-2006, 08:47 PM
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
05-02-2006, 09:09 PM
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 A1:D4, 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
05-02-2006, 10:10 PM
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.

ryrobinson7
05-02-2006, 10:41 PM
Would it be somthing like this?

If Range("A1").Select = True Then
Range("A7").Value = r.Interior.ColorIndex
End If

jindon
05-02-2006, 10:44 PM
Do it like that another hundred years, if you don't want to answer...

ryrobinson7
05-02-2006, 10:48 PM
If Range("A1").Value = 6 Then
Range("A7").Value = Yellow
End If

or

If Range("A1").Select = Brown Then
Range("A7").Value = Yellow
End If

Do that for each color?

jindon
05-02-2006, 11:01 PM
OK

I'll not help you anymore, sice this problem is reagrded as your assingment.

tpoynton
05-03-2006, 04:06 AM
ryrobinson, Jindon has been happy to help you, but your failure to answer the question regarding the purpose of this project is frustrating for Jindon, who has been a great resource for you here. If this is homework, you need to come clean about that...

austenr
05-04-2006, 06:44 PM
I don't usually like commenting on people negatively because I struggle a lot myself but those of you that are trying to help are right. If all you get from the question asker is more code and no explaination of the real problem which is most likely a homework assignment, then this might be a good time to pull the thread or at least mark it closed. Most of what is trying to be accomplished or at least the end result is readily found by searching the net with the search engine of your choice or God forbid using the 'help' files. IMHO. ryrobinson, you are getting colder at your attempts. Here is a hint: look up interior.colorindex in the help files. OK, I am off my soap box now. :hi:

lucas
05-04-2006, 08:32 PM
Well said austenr

jindon
05-04-2006, 09:58 PM
Hi friends,

I might have used a wrong, strong word and I sincerly appology if there is an offended person.

But I really hate those that ignore what he/she should answer, while asking to the others......