Posts by Jong

Is there a function that will swap cell values... or entire columns without having to store the data in a temp location?

you specify the following:

"The subtotaling columns will start from column 8 to that last column with data in that file"

Did you mean row instead of column?
Also, can your data contain spaces between entries?

it would be

AVERAGE(B1, B2, B3, B4, B5) 'etc..

Basically, you just need the cell name to reference the values.

Alternatively, if it resides in another sheet:

AVERAGE(Sheet!B1, Sheet!B2) 'etc..

DISREGARD THIS MESSAGE.

Didn't read the initial msg correctly.

Each bar on a chart corresponds to a series.

To do what you are trying to achieve, you may be able to incorporate some of the below code:

Dim aVals as variant


'First select the chart you are working with
Worksheets("SHEET NAME").ChartObjects(1).Activate


'Loops through each series, checking the values
for i = 1 to ActiveChart.SeriesCollection.Count
    
      With ActiveChart.SeriesCollection(i)
           aVals = .Values


           if aVals(1) > 100 then
                .interior.colorindex = 3
           else
                .interior.colorindex = 4
           end if


      end With
next i

Please note, this code is untested, just trying to point you into the right direction

in order for VBA to read the " character, you will need to use:

Chr(34)

for example:

MsgBox "The " & Chr(34) & " character has been printed"

Also, if your unsure what you final formula will actually look like, try doing a MSGBOX of the formula your setting, and it will show you exactly what is wrong with it

I never encountered this, although do you need to use the Shell to copy folders?

If not, you could also try using "Microsoft Scripting Runtime"

To do so, in your VBA editor, click" Tools > References" and add the reference for "Microsoft Scripting Runtime"

Now you can do the following:

Dim mySystemObject As New FileSystemObject


Call mySystemObject.CopyFolder("\\Server1\setup\tools", "\\Server2\Tools")

I had a similar problem, and to resolve this issue, I did the following:

Whenever a cell is found blank, set the formula of that cell to "=NA()"

A chart will not plot anything if the cell contains #N/A

Try this one out :

=IF('Building Permit'!D55>=10000,250, if('Building Permit'!D55*2.5% <= 112, 112, 'Building Permit'!D55*2.5%)

The problem for macros is related to the users who will be using my software.

Let's face it, not all users are good computer users. Now when the user wants to open a new workbook, he is going to click "File>OPEN" and then it will prompt to Enable macros, which they will probably do, and then all hell breaks loose

So, there is my problem.

I've also thought about not allowing a new workbook to open, but that would just cause total chaos.

I'm trying to come up with a solution, if anyone else has other suggestions, im open ears.

There is another problem with that above code. Whenever you try to close the active workbook, its opens another copy of itself. When you try to close the copy, it does the same. It just keeps spawning copies.

This is a great help steve

Although, there is still a problem.
When the macros of the new workbook get activated, they are also copied into the old workbook.

Any suggestions as to how to get the macros to only open in their respective workbook?

Okay, I've managed to create a new instance of Excel, and to create a workbook in that instance. I still haven't figured out how to load my custom workbook in that new instance of excel.

I have the pathname to the workbook.

Any help will be appreciated.

Here is my code:

Dim objExcel As Excel.Application
    Set objExcel = CreateObject("Excel.Application")
    
    objExcel.Workbooks.Add
    
    objExcel.Visible = True

Can anyone explain to me how to open a new instance of excel using VBA code?

What I am trying to achieve is, IF a user tries to open a new workbook in the existing instance of Excel, the application will open a new instance of Excel, and open the workbook at that location. Up to now, I have the following:

Public WithEvents App As Application


Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    'Code should go here
End Sub

The above code is the event handler for when a new workbook is opened. I need to somehow stop the workbook from opening in my current workbook, and open it in a new instance of excel instead.

Any suggestions will help.
Thanks,

all custom commandbars are created directly in my VBA code... As a matter of fact, nothing is created by hand in this particular application. Everything is dynamically creating via VBA code.

When certain people choose "Save-as" on the workbook I made, and save it as a different name, when they try to run a macro right afterwards, it opens the previous workbook (the old name), and runs the macro in the "old" workbook.

I tried to reproduce the problem on my computer, and I cannot. Although, I have been to the users computers, and the problem persists.

Has anyone encountered this problem in the past?
Any suggestions as to what I could try to do to fix it?

It seems the problem is originating from the MACRO COMMAND BAR that i have setup. The command bar doesn't use the current workbook, but the old one instead.

Thanks.

You'd think they would make this stuff a little more obvious.

Can someone tell me how to edit the text displayed in the column headers (A - ZZ) and the text displayed in the Row headers (1-6553?)

Basically, I have done this in the past, and i do not remember how to do it. I am trying to figure out how to get the text in these sections to be smaller. I'm not sure how to explain what I am trying to achieve.

Let me know if i need to explain further.

Again, i overlook things. I managed to figure it out:

If mFileSysObj.folderexists(ThisWorkbook.Path & "\Help\") = False Then
        Call mFileSysObj.CreateFolder(ThisWorkbook.Path & "\Help\")
    End If

How do you create a new directory in VBA code?

I want to be able to check if the directory exists, and if it doesn't to create it.

ThisWorkbook.Path & "\Help" is an example of what needs to be created if it does not yet exist