Posts by kieranmoore

    I have written a VB app that generates an excel workbook, taking inputs from a third party application. The resulting workbook contains sheets of varying heights and widths.

    When printed using default settings, the result is very messy, with data from the same worksheet spread across a number of pages.

    I want to be able to automate the print formatting (either on creation of the workbook [VB - Excel Object Model], or by the user clicking on a "Format for Printing" toolbar button [VBA macro]), so that users don't have to mess around setting the PrintArea, Orientation, etc. Ideally, I want each worksheet to fit nicely to one printed page.

    I have tried the following, with a varying degree of sucess: -

    For each ws in ThisWorkbook
    ws.PageSetup.PrintArea = ws.UsedArea.Address
    ws.PageSetup.FitToPagesWide = 1
    ws.PageSetup.FitToPagesTall = 1
    ws.PageSetup.Zoom = False
    Next ws

    Is there a better way of doing this?

    The biggest problem I face is automating whether the orientation of a page should be Portrait or Landscape, depending on the number of rows / columns, or the height / width of the sheet.

    Any help much appreciated.

    In Excel 97, it seems that Workbook_SheetActivate() is not called after clicking on a hyperlink, which takes you to a different worksheet. At present, I carry out some processing when switching between sheets. This all becomes 'out of sync' when the hyperlink problem comes into play.

    It seems there is a Worksheet_FollowHyperlink event you can check for in more recent versions of Excel, but this isn't available for Excel 97.

    Any workarounds?


    I have written a VBA concatenate function (see below), which takes a range, concatenates all the strings, splitting each one with a carriage return. The only problem is, that when this displays in excel, each of the vbCr characters displays as a little rectange, instead of a "new line" (i.e. ALT>ENTER). Is there anyway I can get around this?

    Public Function afn_CONCATENATEWITHIN(r As Range) As String
    Dim sTemp As String
    Dim i As Long, j As Long

    For i = 1 To r.Columns.count
    For j = 2 To r.Rows.count - 1
    If Len(r.Cells(j, i).Text) > 0 Then
    If Len(sTemp) > 0 Then
    sTemp = sTemp & vbCr & r.Cells(j, i).Text
    sTemp = r.Cells(j, i).Text
    End If
    End If
    Next j
    Next i
    End Function

    Any help will be greatly appreciated.

    I am generating a number of excel sheets from an external app. Some of the cells contain references to other sheets, however, at the point the cell is populated the sheet if references may not yet exist. In this case, excel pops up a "Find File" dialog, and populates the cell with #REF!. I want to disable this, then refresh all link when all of the sheets have been generated.

    I have a function called afn_CHECK, which basically checks to the total of a column against that of a row. If the totals differ it calls afn_ERROR which tags a comment onto that cell showing the difference. The problem I have is getting hold of the cell that has called the function. Code is as follows: -

    Private Function afn_ERROR(ByVal msg As String, Optional ByVal CellName As Name) As String
    CellName.RefersToRange.Comment.Text Text:=msg
    End Function

    Public Function afn_CHECK(v0 As Variant, v1 As Variant) As Variant
    Dim CallingCell As Name
    Dim CellName As String

    CellName = Application.Caller.Name
    If v0 <> v1 Then
    Set CallingCell = ThisWorkbook.Names(RefersTo:="='" & ActiveSheet.Name & "'!" & Right$(CellName, 5))
    Call afn_ERROR("Cross check error expected " & v0 & " found " & v1, CallingCell)
    afn_CHECK = v0
    afn_CHECK = v0
    End If
    End Function

    The problem seems to be that Application.Caller.Name doesn't return a value in all cases. Any ideas? Any clarification needed let me know.


    Have two problems: -

    1) Is there any way of referencing cells on another worksheet other than using the worksheet name!cellname? My worksheet names are v. long, which makes formulas cumbersome. Is there a shorter way of referencing?

    2) Is there a way of protecting the sheet name so it cannot be changed? The only way I can see is protecting the whole workbook. However, I also want to be able to add new worksheets, which protecting the workbook prevents.

    Any help greatly appreciated.