Replies

Post deleted by gwhitfield

Could you help witht he following, you helped with this code before

Sub NumColors()
Dim x As Integer
Dim i As Integer
Dim ncell As Range
Dim vColors As Variant
vColors = Array(3, 4, 6, 7, 8, 10, 12)
‘Cells to enter dates are labeled as a range “NUM”
Range(“Num”).Select ‘Cells to enter dates are labeled as a range “NUM”
For Each ncell In Selection
If ncell.Value “” Then ‘ If cell is blank then it gets skipped
ncell.Interior.ColorIndex = vColors(ncell.Value)
Else
ncell.Interior.ColorIndex = xlNone
End If
Next ncell
End Sub

This line is causing the problem
ncell.Interior.ColorIndex = vColors(ncell.Value)

I get an out of range error if left that way
If i take out the (ncell.value) it colors everything in as 3(red). If i have 100 different values i need them have different colors based on the array? how can i make 100 different ncell values have at least 30-40 different colors based on what i enter in the array??
thanks for the help.

Ed

Pieterse, I edited your suggestion to thefollowing

Sub NumColors()
Dim x As Integer
Dim i As Integer
Dim ncell As Range
Dim vColors As Variant
vColors = Array(3, 4, 6, 7, 8, 10, 12)
‘Cells to enter dates are labeled as a range “NUM”
Range(“Num”).Select ‘Cells to enter dates are labeled as a range “NUM”
For Each ncell In Selection
If ncell.Value “” Then ‘ If cell is blank then it gets skipped
ncell.Interior.ColorIndex = vColors(ncell.Value)
Else
ncell.Interior.ColorIndex = xlNone
End If
Next ncell
End Sub

This line is causing the problem
ncell.Interior.ColorIndex = vColors(ncell.Value)

I get an out of range error if left alone.
If i take out the (ncell.value) it colors everything in as 3(red). If i have 100 different values i need them have different colors based on the array? how can i make 100 different ncell values have at least 30-40 different colors based on what i enter in the array??
thanks for the help.

WORKS GREAT! tomorrow will be the last day of my internship so you may not see too many more of my questions. I greatly appreciate your help. ALL OF IT!!! thanks so much.

Hey Andrew that’s what I’ve been looking for. It allows me to overwrite a list validation based on your input. The only thing is I have 1 button assigned to the macro that contains the Input Box. When the info is entered it becomes pasted to cell A2. I know i can change cell A2 to have it append to wherever i want. How can I make it append to a specific cell that is either highlighted or slected by the user?? thanbks

the cells can have values from 1 to 250. so i used i to count them for me. i only pasted a portion of my code, it extends a lot further. i know there is a cleaner way to do this but im still learning so i take what i can get, your knowledge worked perfectly, again.

Ed

Thanks Tom

A note for you, the first line with the appExcel didnt work when i changed it tp GetObject from Create Object. The error was that Active X could not GET. I just reverted back to the old one and it worked fine. The close statement is good now too. Here’s a little more food for thought, if after opening that main file in excel from access could i enter a line of code to run a macro ive made in excel? so it would be open dummy, open main, run xls macro, close dummy? Anything like that even possible?

And i must add that your da man!!!

i dont think its the file name thats the problem because the program opens the dummy file with no problem. ive tried it the other way and i still get the same thing. maybe there is just a problem with the wording of the line:

appExcel.Workbooks(“G:NEWIDE~1Dummy.xls”).Close False

If the other commands look like appExcel.Workbooks.Open…, why doesnt this one have appExcel.Workbooks.Close… I’m just wondering????

Any more clues?? Thanks

Even with the fix to the close statement i still get an run time error 9′ – subscript out of range and it highlights that line

appExcel.Workbooks(“G:New IdeasDummy.xls”).Close False

ANy clue what this is or why it happnes? Thanks alot. If this can stop then it’ll be perfect, i hope.

Ed

WORKS GREAT!!!! can’t thank enough.

Does the job perfecty but i get the #VALUE error in all cells that do not have dat on DATA ENTRY SHEET. I tried an IF ISERROR but i couldn’t get itt o work. That was something i learned here. Am i trying the right thing? Is there a way to not display the # VALUE? So close i can smell it….
ED

Here you go, more details are on the INFO tab. thanks.
Ed

For some reason i am getting a run time error 13 problem when i use the code. This is what i am doing. A name gets selected from a drop down list on sheet 2 in the format ( First, Last). i then need to have this appear as (F. Last) on sheet 1. I figured i could copy the info from the list in sheet2 into sheet 3 and run the code from sheet 3.i could then copy sheet 3 into sheet 1. nobody sees sheet 3 so thres no harm. i cant just run the code in sheet 1 because those cells have the formula

=’Data Entry’!I3 & CHAR(10) & ‘Data Entry’!J3 & CHAR(10) & ‘Data Entry’!K3
it appears VB wont run the code because the cell refernces take presedent over coding format, is that true? either way when the code gets entered into sheet 3 and i try to copy and paste the info from sheet 2 the line

strCell = Target.Value

gives me an error.

What am i doing wrong????

Never mind the middle initial stuff, not using that anymore.

Hi Andrew the code works fine once i fixed it for the range i needed. Your directions were great. If a person had a middle name could i edit this could to display it as J. D. Smith. So its First Initial., Middle Initial., Last. Sorry for the bother, but its something I just realized migth be a problem. You have no idea how much youve helped, thanks.