Replies

Microsoft’s KnowledgeBase recommends saving screwy tables as RTF file format and then opening them again to a Word doc to clean out the lint. Obviously, this might leave something behind, but it might be worth a try. But why “newly-created” tables, too? Hmmmm….

Also see:
http://support.microsoft.com/search/previe…b;en-us;Q287398%5B/url%5D

Your error message is complaining that “Type” isn’t an argument of “Controls”. You need to have an actual control assigned to arg1, not a collection of controls. Maybe you were trying to use the FindControl method?

Anyhoo… this code will wipe out all combo controls on the commandbar:

For Each ctrl In CommandBars("Filter Form").Controls
    ' You can also check the values of ctrl.Caption, ctrl.Visible, ctrl.OnAction
    If ctrl.Type = msoControlComboBox Then
        ctrl.Delete
    End If
Next

Hmmm..don’t know about AS400. Queries are OK, but reports complain?

When I had trouble yanking data out of Lotus Notes via ODBC, I used “make table” and “append” queries to make my own data table in Access from the Notes data. Gave the user a button to click to delete and recreate that table as needed. Another button ran the report pointing to my new table.

I’m not an XP user yet, but from the sound of all this, when I do upgrade I should set up my own User Templates directory pointing so some place I can easily get to. (I hate long complicated paths!)

Is that a reasonable suggestion for XP users? A new NORMAL.DOT file would be created there?

Maybe you could describe what you’re trying to do in the workbook (more generally) and someone will suggest an entirely different approach that avoids this irritating little listbox altogether.

Try this Gary:

    Dim pvt As PivotTable
    Set pvt = ActiveSheet.PivotTables("PivotTable1")
    For Each itm In pvt.PivotFields("Code").PivotItems
        itm.Visible = True
    Next itm

This code WILL error when it gets down to one item left in the pivot table. You have to have at least one item visible. So you can decide how you want to handle that. But this should get you started…

You could put a condition to evaluate the itm.Caption and only deselect certain items. Then you could have toggle buttons that turn sections of your WBS on and off in the pivot instead of all or nothing (which won’t really work anyways).

Enter a table like this instead of the one you have now:

	Col A		ColB
12	Hired after	Vac Hrs
13	1/1/65		160 
14	1/1/88		120 
15	1/1/93		80 
16	1/1/01		40 
17	7/1/01		0 

Then use this formula to choose the number of vacation days. Cell I4 is the start date.

=VLOOKUP(I4,A$13:B$17,2,TRUE)

This means, lookup the start date in the table (A13:B17) and get the value in the second column. The “TRUE” parameter means, “If you can’t find an exact match use the highest value without being greater than the start date.” This is the same kind of lookup you’d use to figure out your tax rate on your income taxes. You lookup your income in a table that has cutoff points like this and pick the highest value without being greater than your income.

Put the dollar signs in the formula to prevent that range from “slipping” when you copy the formula down the column for other employees.

The user is out of compliance and should be terminated. Problem solved! Just kidding.

You could check the Tools…Options…Compatibility settings and see if anything is checked in there that might affect formatting.

Any tables in the cut/paste content? RTF has various problems with tables. If you describe the text that is being pasted someone might have a better idea. Just text? Wrapped or single lines? What style and format?

MsgBox Dialogs(wdDialogToolsOptionsCompatibility).Product

Hi Hans, I’m no expert on VB performance so I’m curious about how your version runs faster. What differences improve the performance? Maybe I need to give some of my production code a makeover, too!

Here’s two suggestions:

You can use this function to represent a date in your formula:
DATE(1998,1,1)

You could put the date in a cell and not in the formula. Do you have a column with the start date? Formulas can reference dates in cells without any problem and the same formula will work for every employee row. Typing each employee’s start date into an individual formula sounds like a lot of work!

Wow, I really wandered off the compound there! Thought I was in Wordland.

Back on topic, I like Legare’s suggestion of using buttons on the Quick Launch toolbar. Keeps it simple. A lot of my users are happy using buttons on the Microsoft Office Start Toolbar that resides along the right side of their Windows screen. These are usually templates, but could point to any spreadsheet.

The tricky bit is keeping all these updated centrally if you have a lot of users and the filenames change or new buttons are needed periodically.

I had a client recently that demanded a semicolon-delimited “CSV” format for their data AND no quote marks please. Ugh.

After much wasted time trying to remove all semi-colons from the content prior to export and then writing code to open the text file and search replace commas to semi-colons they finally realized that this just wasn’t going to work without wrecking their data.

Hurrah! We’re going to use standard CSV files right? No! They want pipe-delimited now. Still no quote marks. I think the inability of their importing software to handle the text-delimiting quote marks means that they cannot use a field delimiter that could possibly exist in the content itself. Thus, they chose the oddball pipe character: |

So I’m curious about what software wants these semi-colon delimited files. Is this a trend?

You could use a “sorting column” with a formula like this:

=Sortstring(A1)

which will turn the text “C.3.34.1.2” into “C03340102”
and “C.12.1.1” into “C120101”, etc.

Never heard of the function Sortstring(cell)? That’s cuz I made it up! It’s a custom function you can paste into a module in your workbook. It’ll pick out the numbers between periods and format them to 2 digits and concatenate them into a string that’ll sort as many levels down as you need.

Function SortString(sInput As String) As String
  SortString = Left(sInput, 1)
  For x = 3 To Len(sInput)
    char = Mid(sInput, x, 1)
    If Asc(char) > 47 And Asc(char) < 58 Then
        tmpNum = tmpNum + char
    Else
        SortString = SortString + Format(Val(tmpNum), "00")
        tmpNum = ""
    End If
  Next x
  SortString = SortString + Format(Val(tmpNum), "00")
End Function

Weird. But it works in a pinch — like this. Hide the column or delete it when you’re done.

“…almost every time someone enters the next code for the next group of tags …”

I’m not sure what that involves exactly, but if it’s really the action that causes “page layout amnesia” it might be worth looking into what happens at this stage. Even the most harmless steps could be causing this. I have seen “page layout amnesia” problems in shared Access databases when users have different printers defined. One user does Page Setup for their HP5. Next user doesn’t have same printer and Access defaults to standard page setup. Grrrr….