Replies

Thanks John, I’ll give it a go tomorrow. They seem to be something in the right direction.

Suggestion #2 – “Multiple Excel Files…” worked like a charm (Thanks again Hans / :rolleyes:)

Thanks for sharing the insights Rory.

Trying to understand my options in O2010 from both the programmatic and the GUI side.

So if I understand your comment and the documentation correctly, I can set up a theme with a set of basic colors and vary those with ao. the .tintandshade property of the .interior object?

Additionally I can also vary any color directly regardless of the theme with the same .tintandshade property of the .interior object?

Hope this makes sense… :huh:

So the .Colors thought is gone and you just apply specific colors at specific instances??

So it’s not really possible to supply a user with a detailed set of “approved” colors without:

1. changing the theme?
2. having only 6 colors available? (urghs!)

“…so I’m not sure what your question really is”.
Probably because I took it slightly out of context to simplify (Maybe I should have posted in VBA forum)

Actually I’m trying to setup themes 1, 2 and 3 that all has varied colors with a lot of gradients.

In my old VBA code I did this with a toolbar, where the user selected a “color pack” that was then applied to the entire color palette. All done by means of a “For… loop”, that ran through the .Colors property of the ActiveWorkbook (below).

dim iColors as Integer
For iColors = 1 To 56
With ActiveWorkbook
Select Case iColors
Case 1
‘BLACK
.Colors(iColors) = RGB(0, 0, 0)
Case 17, 25, 53
.Colors(iColors) = RGB(0, 65, 120)
Case 22, 30, 52
.Colors(iColors) = RGB(155, 140, 100)
…..

In O2010 it seems to me that the .Colors property is no longer working in a meaningful way as the color palette has been changed significantly. So my question is:

How do I implement code that does what my old “For loop” did – and what should it look like??

I’ve tried to record a macro, by creating a theme. But it does not reveal:

1. how the colors are set
2. how I get more than 6 accents

Hope this is more clear :confused:

You can create a UserForm which can be done in the VB Editor in Excel.

Thanks for the answer – appreciate it, but I’m already aware of that option. I was just hoping to achieve more or less the same with the “input box” directly, so that I don’t have to go through the tedious “VB-forms” way.

Hi Rory,

Why use the subset .ProtectContents in comparison to “just” .Protect – after all .Protect includes .ProtectContents as well?? (fwik)
:confused:

Hi jscher,

Thanks for the tip. Only problem is that this is directly between Excel and Word, so for now there’s no ODBC source involved.

Hi Rory,

Got you on that – thanks for the tip.

I have decided to take a slightly different approach with a fast double iteration, in which I first find the exact number of matches (the UBOUND – then I Redim in accordance with that before entering the loops.

Code looks like this now

e = 1
eMax = 0

For c = 1 To UBound(varGetArrayTransactions, 1)
If sCoName = varGetArrayTransactions(c, 2) Then eMax = eMax + 1
Next c
ReDim arrTransList(eMax, UBound(varGetArrayTransactions, 2))

For c = 1 To UBound(varGetArrayTransactions, 1)
If sCoName = varGetArrayTransactions(c, 2) Then
For d = 1 To UBound(varGetArrayTransactions, 2)
arrTransList(e, d) = varGetArrayTransactions(c, d)
Debug.Print “Item (” & e & “,” & d & “): ” & arrTransList(e, d)
Next d
e = e + 1
End If
Next c

Appreciate your efforts on this.

Hi Rory,

Not sure I understand what you mean by “Make arr2 a 1D array of arrays” – got an example?

I tried the transpose bit for starters, but I find it to be a bit messy to work with, when I have many “rows”.

Hi Rory,

Not quite sure that I understand what you mean by that… , but I’ll try to explain.

Combo1 – array of 50 rows x 12 columns
– user sees only column 1 and selects an item from there. All other cols contain – in database terms – duplicate values.

Combo2 – currencies (fed from named range “dnCurrencies”, 8 items)
– is also found in combo1 array in column 2 (so company X has a corresponding currency, Company Y may have the same CCY)

Combo3 – countries (fed from named range “dnCountries”, 20 items)
– is also found in combo1 array in column 3 (so company X has a corresponding country, Company Z may have the same country)

So when user changes in Combo1, I get the .listindex returned and can see corresponding values for Currency and Country in columns 2 and 3 respectively.

Then somehow I need to lookup these values in the other combo’s lists and once I’ve found their .listindex value then set it.

So questions are:
– How do I make the latter in the most efficient way?
– Should it be done on a change event?
– Do I need to link the events somehow?

What would be the “best practise” approach on this? (if there is one – )

Thanks Rory,

Problem is not so much the first addition of the lists. It’s when the value in combo1 changes that I’m in trouble…

Then I need somehow to
1. lookup the related values the array making up combo1 (the hidden columns on the control)
2. match the values from each column in combo1 with a corresponding value somewhere in the list of each of the other combos
3. set the .listindex on combos 2-4 to their corresponding new number

…i guess

Thanks Rory,

Normally I don’t do it either, but problem here is that .activate gives me the current selection – which is the entire range (B6:F19) and not just a single row.

Therefore I decided to try the .select instead. Problem is that it creates another “problem”.

Any idea to how I might be able to get around this??

A really informative article.

Thanks for the input RG – I appreciate it.

Despite the clarity of the article, I have an example below where I just don’t understand what’s going on. Maybe you can explain it to me…?

Got a small loop counting down and an array filled with data:

For d = UBound(varGetArrayAll, 1) To 1 Step -1
Range(sRangeAddress).Select
Selection.Rows(d).Select
Debug.Print Selection.Rows(d).Address
next d

I want to format a range, say “B6:F19” (named: “sRangeAddress”). Within the range, different rows have different formats. (eg. B6 is title, B7 is heading, B8-B17 is data. B18 a source and B19 a comment. All number of rows can vary)

“Selection.Rows(d).Select” selects the right row.
But “Selection.Rows(d).Address” provides an address offset with d to the range.

What I don’t understand is how the same “question” –
Selection.Rows(d) – can result in two different addresses…

Any clues??

Bests

PS:

Found an answer to my question…

Public Function Array_FillFromSelection()
Dim c
Dim d
Dim varGetArrayAll As Variant

varGetArrayAll = ActiveCell.CurrentRegion.Value
For c = 1 To UBound(varGetArrayAll)
For d = 1 To UBound(varGetArrayAll, 2)
Debug.Print varGetArrayAll(c, d)
Next d
Next c
End Function