Posts by NathanC

Re: Quote Marks

Use: =COUNTIF(S86:BF86,"1/2"[COLOR="Red"]"[/COLOR] HOLE")

The first quote mark (of the two in a row) acts as an escape character [if you're familiar with the term].

Re: Prevent Save as Version Message

Quote from Dave Hawley

you will get xlNormal when you record a macro saving as normal. What else would you expect? Record SaveAs saving as the needed version.

Yes, there are aspects which I don't know. I never claimed to know everything.

Quote from Dave Hawley

Also, if you put FileFormat into the VBA help you will get a list.

The VBA help files are not installed on my personal computer - so I have no way of knowing this.

Quote from Dave Hawley

I suggest you drop your Assumed Experience as it's not at the level you assume.

And I suggest you use some Interpersonal Skills and learn a little TACT. I have developed over 5,000 line applications for several different businesses and have a degree in Computer Science, working towards a Master's. For the most part, I generally know what I'm talking about.

Sorry if this comes across rude, but in this and another one of my posts recently you have responded offensively.

Re: Print Data Without Any Cell Colors

Anybody have any ideas??

Re: Prevent Save as Version Message

gmccreedy,
When I record the macro, all it puts for the FileFormat parameter is: "xlNormal" which doesn't really help me a whole lot. Is there a list somewhere of the various save formats?

Also, why does "Application.DisplayAlerts = False" not affect it?

Re: Transition Between 2 Different Office Programs

Bryce, Thank you so much. This will give me a good start to work with.

Norie, I will post an example as soon as possible. I don't currently have an applicable one with me - I'm at a friend's house now. Thanks for all your help as well.

Re: Transition Between 2 Different Office Programs

Quote from norie

You could easily automate Word from Excel.

Okay, Thanks again. I'm not sure at all how to do that. Could you maybe give me something basic that I could expand on? I know enough to experiment and edit once I have the basic concept.

Re: Prevent Save as Version Message

I have something similar but it still comes up with the message. Is the key in the:

FileFormat:=xlExcel5

Which versions do the FileFormat numbers (2-5) correspond to?

Re: Transition Between 2 Different Office Programs

Okay, Thanks. I'll have to experiment with that. However how would I go about running the Word code from within an Excel macro?? That's where I'm having the most trouble/confusion.

Re: Saving Under Earlier Version

Quote from Dave Hawley

...capture the save and force the version on them

I'm not sure I'm following what you mean here. Could you please explain a bit more. Thanks.

Re: Before - After Print (and Save)

Quote from Dave Hawley

Please use Thread Title to state what you THINK is your answer!

Sorry Dave, I wasn't sure what to call it or how to explain it.

Quote from Macropheliac

Well, someone else may come up with something better, but you could insert a new sheet, copy the sheet to be printed to the new sheet, remove the shading from the new sheet, print the new sheet, and delete the new sheet.

Macropheliac, the idea itself isn't bad but it still has the same problem. The thing is that I want the colors cleared out anytime the standard "Print..." option is used then restored once the printing is finished. I'm not doing it manually - the catch is that there is no way to know at what point to delete the sheet. There is no event or trigger that "activates" once the printing is done. Thanks for the idea though. Any more ideas from anyone??

Re: Transition Between 2 Different Office Programs

Okay, writing the code to make a second table of only selected items would be easy enough and makes sense. The button is on the Excel Sheet and needs to: open Word, setup proper formatting for the Avery Standard labels, then fill in the table cells with the appropriate information from the column(s) in the Excel Spreadsheet. I know how to do the code within Word to setup the label formatting but I don't know how to run it from a macro in Excel or how to open Word in the first place from Excel and of course how to fill in the Word table with the Excel data. Let me see if I can come up with a simple example sheet.

Re: Before - After Print (and Save)

Would that be the same as printing in grayscale? If so I still want to clear it because it would still use up more ink than necessary.

Re: Transition Between 2 Different Office Programs

I'm not too familiar with Mail Merge. How would I go about doing it.

Also, I only want to do specific ones out of the list (selected by the user) and only done upon clicking on a button. Is this possible with mail merge?

I have an extensive application which I wrote using Excel 2002 (XP - Version 10). However it will likely be used on anything ranging from 2003 - 1997. When I tested it using Excel 97 it gave me the traditional [annoying] message asking if I was sure I wanted to save it under an earlier version than which it was created.

I want to get rid of this message (a lot of my users aren't the most computer literate and the more annoying messages I can avoid the better!)
I tried using:

Application.DisplayAlerts = False

but it doesn't affect this particular message. I need to know how to keep this message from popping up. Please Help.

P.S. Under 97 (NOT on my 2002) it also gives a "Do you want to save the changes" message upon closing even though the "DisplayAlerts" is set to false. I'm not sure why this is still coming up. Any help would be greatly appreciated. Thanks.

Within the Workbook events there is a "Before_Print", however I also need something like "After_Print" which does not exist (as far as I can tell).

I have several rows of data which alternate colors. When I print it I want to take out the fill (so as to save ink) - which I can do in the "Before_Print" event. [No problem.] However, once it is done printing I need to restore the alternating colors, but I cannot think of any event or trigger which would enable this.

I have thought through several things and the closest thing I can think of is setting up a timer. But there is of course no way to now how long to set it for. [Before/After Save also has the same "issue".] Any ideas would be greatly appreciated.

I need to know how to open and manipulate a Microsoft Office document using code in Excel.

I have a list of Names in an Excel column that I am trying to paste into a Word table (formatted as Avery labels). Each name goes in a different table cell in Word. Example: Copy from "B5" in Excel and Paste into the first cell in Word, copy from "B6" in Excel and Paste into the second cell in Word, etc.

I know the code needed within Excel and I also know the code in word used to setup the table format. I just need to know how to "call" it from within Excel and how to copy/paste back and forth. Please help. Thanks.

Re: Hide some sheet tabs

Quote from jamierodgers

ive got a workbook with 13 sheets. I only want 7 of these tabs viewable but still want the sheet to be viewable by clicking on a button

You could use code (on workbook_open or something) which hides the ones you want hidden. Then when the button is clicked it could unhide the sheet and select/activate it. Upon worksheet_deactivate hide it again.

Re: sheets statistics

Use something like the following:

Sub CountSheets()
    Dim ws As Worksheet, colorCount As Integer
    
    colorCount = 0
    For Each ws In ThisWorkbook.Worksheets
        If ws.Tab.ColorIndex = 3 Then colorCount = colorCount + 1
    Next
End Sub

Re: Column Formula

Here is along the lines of what I was thinking:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Mid(Target.Address, 2, 1) = "A" Then 'Column Letter
        RowNum = Right(Target.Address, 1)
        
        If Range(Target.Address) = "" Then
            Range("B" & RowNum).ClearContents
        Else
            Range("B" & RowNum) = "=A" & RowNum & "*10"
        End If
    End If
End Sub

Adapt as needed. I hope it helps you out.

Re: Column Formula

You could make a short VBA code that dynamically adds the formula into column B of a row as you add that row.