Replies

Getting rid of hidden text. I didn’t quite understand why you needed a two stage process – one does it fine on my version of 2000 or 2002.
What’s wrong with simply searching for character format = hidden, and doing a ‘replace all’ with the null string?

If it is a VBA requirement then

Selection.Find.ClearFormatting
Selection.Find.Font.Hidden = True
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = “”
.Replacement.Text = “”
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

Should do it.
The find-all variant, as a Macro, looks simpler because it is not followed by a filtering method such as execute.replace

Selection.Find.ClearFormatting
With Selection.Find
.Text = “fox”
.Replacement.Text = “”
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Cut

OK – should be straightforward.
A design question though.
Presumably your ideal solution would automatically detect the right kind of attachment – if so, how?

I always wondered what SUBTOTAL was supposed to do.

I also found that stability was affected by operating system.
If you’re on Windows XP – great. Millenium Edition is a recipe for these kinds of errors.

Geoff

Use the normal Edit > Find and fill in your search criteria

Tick the check box labelled “Highlight all items found in:”

Click the button that now says “Find all”

Voila

It could be coded as a VBA Function

However, if there are only a limited number of possible functions e.g. less than about three – then a simple IF statement will do it.

=IF(A31=”Average”,AVERAGE($B$2:$B$30),IFA31=”Min”,MIN($B$2:$B$30),MAX($B$2:$B$30)))

(I used MAX for the case that A31 wasn’t “Average” or “Min”)
If the above were in B31 it could be copied to B32 and other cells like it.

Try the following where j is the offset – (currently 14)

Private Sub Worksheet_Activate()
ActiveSheet.Range(Cells(14, 1), Cells(ActiveSheet.UsedRange.Rows.Count, 3)).ClearContents
i = Worksheets(“Sheet1”).UsedRange.Rows.Count
j = i + 14
Worksheets(“Sheet1”).Range(“A1:B” & Format(i)).Copy _
Destination:=ActiveSheet.Range(“A14:B” & Format(j))
ActiveSheet.Cells(j, 3).Formula = “=SUM(Sheet1!C:C)”
End Sub

Note that the routine fully cleared the sheet prior to loading – this was a quick method.
I assume you’re trying to preserve the first fourteen rows and am therefore only clearing columns A:C from 14 down to the EXISTING last cell

Andrew

I’m struggling to understand the problem.

So far I think that :
You have a single template.
You use this template to create a Workbook for departments.
This single created workbook represents a single form which is mailed and saved multiple times, but only printed once?

I cannot quite understand if the ‘form’ is in the workbook and always the ‘same’ form or not.

Frankly if there is a single form in a specific workbook the easiest way to achieve uniqueness is to simply name the workbook with the formname as in

Form-12345.xls – where 12345 is the form number

Within the sheet a formula such as

=MID(CELL(“filename”),FIND(“.xls]”,CELL(“filename”),1)-1,5)

would then ‘extract’ the 5 characters just prior to the end of the filename (for numbers of other lengths change the 5 to something else.

However, once again, I’m working on a lot of assumptions due to the fact that I don’t yet understand the problem so will not attempt a detailed robust ‘solution’.
If the above is not close, could you clarify what you think of as a form and describe its “life”. e.g. describe what it is, how it is created , how it is mailed and changed, and when the number is ‘set’.

Thanx in advance

Legare
True – the routine I proposed would increment every save – I wasn’t putting too detailed a job into it until I found out more about the problem.
It would be easy enough to only increment on a Saveas (or force one). I also considered that one could use the file NAME as the form counter and increment it via a Saveas as well

I made the assumption that MyFormCounter was the name of the Form number – VBA code didn’t have to do anything with it.

I liked all your questions – the answers will help work through several of the assumptions I made explicitly and implicitly

If I interpret your description properly – try this as an event procedure on the manifest sheet.
It should accomplish the copy each time you enter the sheet.

Private Sub Worksheet_Activate()
ActiveSheet.Cells.ClearContents
Worksheets(“Sheet1”).Range(“A:B”).Copy _
Destination:=ActiveSheet.Range(“A:B”)
i = ActiveSheet.UsedRange.Rows.Count
ActiveSheet.Cells(i, 3).Formula = “=SUM(Sheet1!C:C)”
End Sub

Intriguing question – but rather depends on what you think of as a form and filing.
For the sake of a reply I assume the form equates to a master workbook used as a template.
I also assume that once you ‘save’ the workbook this equates to ‘filed’. After such a save
you want the next workbook created from the workbooke to be based on the template +1 number?

Should be easy with a BeforeSave Routine
The Counter could be stored in a protected cell, or in a Document Property of the original template.

I’ll assume it is in a cell with name “MyFormCounter”

The following event Macro should do the trick

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Selection.Locked = False
Range(“MyFormCounter”).Value = Range(“MyFormCounter”).Value + 1
Selection.Locked = True
End Sub

Wendy
Your used “File > New > Post in this folder”
This feature is designed for use with ‘Public Folders’ which are Microsoft Exchange equivalents of bulletin boards.

Sorry – all my searches turned up a blank too.
I assume that you’ve trolled through its properties and stuff like that? Can it be treated like mail – e.g. encapsulated and forwarded – I’d be intrigued to have a look at the offending item.

Arage

The short answer is YES . The slightly longer one is MAYBE .

It all rather depends on what needs to be achieved specifically and how strongly structured your data is.
Can you post a (sanitized if necessary) example of what you receive, and what you want it to become.

Just being cautious – generally when I get mail items that I don’t remember creating I suspect one of two possibilities
1) I’m having a ‘senior’ moment
2) Virus activity
Neither seem to apply in your case