Replies

What happens when you start Word and File…Save…and scroll in the folder list? Same thing?

I wonder if it’s the scrolling or the Open or what???

It is almost impossible to get a good RTF output of an Access report. The RTFdocument does it’s best but something is always out-of-whack. Rules disappear, line spacing changes pulling the footers up about an inch or 2, memos get truncated because word-wrap is different but field “height” is fixed, hard returns appear, etc… Which is why Microsoft added the File…Export…Save As…Snapshot and the Snapshot Viewer. It’s like a PDF. It retains formatting, unlike RTF export.

You’re probably going to have to compromise somewhere along the line. Microsoft does have a fix for the truncation problem with Memo fields in Access 2000 you can download:

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

We set up a macro that dumps out all the Autotext names and entries. By using a Style (GreenUglyCourier) for the Autotext name and no style for the actual Autotext content it’s easy to separate the two while reviewing. We added an “Update Autotext” macro that lets the user make changes to the Autotext contents right in the doc and then click a button to update it. Update macro:

1. Checks to see if you’re on text that has style GreenUglyCourier (if not then quit w/msg)
2. Selects and stores current line text (this is the name of the Autotext)
3. Goto next line down, select down line by line until it hits a GreenUglyCourier style line.
4. Store Selection as Autotext name.
5. Move down a line to the next Autotext so user can click macro button again to update the next Autotext.

This process makes it really easy for our end users to update a bunch of Autotexts. Two buttons:
1. Deletes everything in the current doc and then types all Autotext names/contents
2. Update currently highlighted Autotext.

Sorry. It gives me the heebie-jeebies to think that the DOBs are already available but will be retyped — as text. Whatever. As long as Tammy’s happy and find it a brilliant solution, that’s what counts!

(The old Copy…PasteSpecial…Values trick would let you remove the Date of Birth column after calculating the “yearless Birthday” text, if necessary.)

While your waiting, see what you think of this method.
Since you have headers and total rows where column C is blank you don’t want to hide ALL rows where C is blank, just those in the data area of your spreadsheet. So give that area a range name. It’ll grow as you add new employees to the sheet. Or you can just specify the exact rows to “trim down”.

Sub PrintNonBlankDataRows()
'These two rows figure out which rows to check for blanks
'You need to select the print area to "trim down" and name it Data
    FirstRow = Range("Data").Row
    For iRow = FirstRow To FirstRow + Range("data").Rows.Count
'If you just want to specify rows by number, use comment out lines above and use this line:
    'For iRow = 14 To 36

        If Range("C" & iRow) = "" Then
                Rows(iRow).EntireRow.Hidden = True
        End If
    Next iRow

'Print or print preview
    ActiveWindow.SelectedSheets.PrintPreview

'Unhide rows
    ActiveSheet.Rows.EntireRow.Hidden = False

End Sub

Sorry, if I misunderstood the question. It did mention “user input” — something that normally requires the user to be present and accounted for at the computer when it runs. Maybe its just supposed to fly up onto the screen once and hour and ask them something.

Sometimes people ask for a specific technical solution without explaining what their real goals are and I take them too literally and provide misleading info. “Automatically” running a program sure sounds like unattended running — but maybe what the poster really means is “don’t make my user open Excel and then open a file and then click a macro button”. I don’t know. Maybe we’ll find out….Kevin?

To set the focus to the Listbox itself all you need is:

lstSpeed.SetFocus

To set the default selected line in the list box:

If the form is a data entry form with a listbox “bound” to a field, define a default for the field in the table. Then it will automatically appear selected in the listbox when you add a new record. You don’t want to wreck the existing record when they display in the form by forcing the listbox value to change.

If your listbox is NOT displaying database field, just getting user input (not bound to a dataset) then try this:

List0.SetFocus
List0.ListIndex = 1

If I don’t setfocus first I get an error message. If you don’t want that to be the selected control than add another line to setfocus to another field. This may not be the best way but it’s workin’

http://www.google.com/search?q=site:www.vbsquare.com+listbox%5B/url%5D

http://www.windowatch.com/vbtut7.html%5B/url%5D

http://searchvb.techtarget.com/%5B/url%5D

Sub ChangeDocTitle()
      'Get the title from fields, prompts, date, etc...whatever!
      newTitle = ActiveDocument.FormFields("AnyFormField").Result  
      With Dialogs(wdDialogFileSummaryInfo)
         .Title = newTitle
         .Execute
      End With
End Sub

Don’t bother trying to set the Title property directly in VB. If you don’t go through the dialog box it doesn’t always work. V. frustrating.

Pivot Table. Definitely. Especially if some household acquires a pet alligator or hedgehog. Pivots automatically expand.

Are you trying to find a way to enter a checkmark character with a shortcut? I don’t even have that character in my Insert…Symbol…unless I use the Wingding font on character:

Try not using the wizard. It does irritating things like setting up relationships between your lookup field and the source table without telling you. It includes an ID field in the lookup row source even though you didn’t select it.

Instead set up the lookup yourself on the bottom part of the (Table Design?) screen. Choose the tab called “Lookup” instead of General. You can fill these fields in yourself.

Display type: Combobox
Row source type: Table/Query
Row source: SELECT Customers.CompanyName FROM Customers ORDER BY Customers.CompanyName;
(You can use the query builder to design this SQL graphically by clicking the […] button here)
columns, etc…

..

Edited by DoryO on 19-Jun-02 16:38.

I have to agree with John. As soon as someone utters “we don’t need those entered as dates, text is fine” someone asks for a new report by date. Also, your users won’t enter something goofy like “06-31″ which is not a valid date.

How about a sorting column filled with this formula (if Col A has the true dates):

=TEXT(A1,”mm-dd”)

This returns text you can sort by regardless of year.

{Edit: I just noticed a previous post with similar suggestion. Sorry to repeat it. But, seriously, Tammy — avoid that leading-apostrophe method of entering values as text. It seduces so many.}

Edited by DoryO on 19-Jun-02 16:22.

Sub Proper_Case()
‘ Loop to cycle through each cell in the specified range.
‘ There is not a Proper function in Visual Basic for Applications.
‘ So, you must use the worksheet function in the following form:

For Each x In Selection.Cells
x.Formula = Application.Proper(x.Formula)
Next

End Sub