Posts by Bryan021

    I have a cell at the bottom of the page with some fine print verbiage that is all formatted as Calibri 7, with some characters Bold.
    It appears correct onscreen, and prints correctly... I save the file and it still looks good, but if I close and reopen the file, the font returns to an 11 size, but retains the font type and bold... this pushes the end of the line off the page.
    Any idea what's going on with this?
    Your input is appreciated.

    Re: Populating ComboBox with unique values (case sensitivity)


    Never mind... the answer is right in front of me... LCase... get rid of it... Duh...


    Working code;

    I have the below code to populate ComboBox1 on UserForm activation, and ComboBox2 on ComboBox1 change.


    The referenced Range (Column B) has names in propercase, but ComboBox1 populates in all lowercase, so ComboBox2 never populates because the lowercase ComboBox1 value doesn't match the range content in propercase.


    How do I get ComboBox1 to populate identical to the range content?



    TiA,

    Re: Workbook_Open() not firing


    I found the cause of the mouse-over and data validation dropdowns not working.
    On the 'Original' sheet, I have snapshots of sections of the 'Data' sheet to show company and client info.
    If these snapshots reference ranges on another workbook, things are fine, but once the reference is to a sheet/range within the same workbook, the mouse-over and data validation dropdowns stop working.
    I deleted the snapshots and all is well again.


    I'd still like some insight as to why this is occurring, and how to get snapshots without having this problem.

    Re: Workbook_Open() not firing


    No comments or suggestions... I'm a little surprised from this group.


    Anyway, issue is solved... I started over with a fresh workbook and redid everything, which now does as it should.


    I'd still be interested in theories behind the cause, as it would be a real SoB if I was further into a project and had to start over to get a fix.

    Workbook_Open() subs are not firing if I have another workbook open.
    The sub does fire if no other workbooks are open, but if another workbook is open, it doesn't fire.
    This is not workbook specific... it happens to any workbook with a Workbook_Open() sub.


    Code
    Private Sub Workbook_Open()
    'Load Initial UserForm
    
    
            If ThisWorkbook.Sheets("Project Info").Range("F2").Value = "" Then
                InitializeUserForm.Show
            End If
            
    End Sub


    Thoughts on what could be stopping the Workbook_Open sub from firing?


    TiA,

    Re: Export to PDF - page orientation not passing


    OK... Solved.


    It must have something to do with page margins.
    Even though it previewed and printed correctly to the local printer, by minimally reducing the width of some columns (I'm only talking 6pix total), it now PDF's with the correct page orientation.

    Re: Completely lock a partially locked sheet?


    Stephen,
    Thanks for the response.
    That is essentially what I was originally after, but with the benefit of time, I now think that might be an overkill, as it is not undo-able (you can't return to the prior combination of locked/unlocked cells).
    For now, what I have will do the job, and is reversed by deleting the contents of A1 (not that the user knows this).

    The following code I've successfully used in many workbooks, for many years;

    Code
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FldrPth & "\" & FlNm, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False


    I have a new workbook that has a landscape page orientation set in Page Setup.
    It prints correctly, but when exported to PDF the page orientation is portrait, causing the sheet to spill onto two pages wide.
    I've tried adding;

    Code
    ActiveSheet.PageSetup.Orientation = xlLandscape


    Before the export, but it makes no difference.


    Any ideas what can cause this issue, and potential fixes?


    TiA,

    Re: Completely lock a partially locked sheet?


    In lieu of any suggestions, I've used the following code on the sheet in question;

    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Range("A1").Value <> "" Then
            If Target.Address <> "$A$1" Then
                MsgBox "This Sheet has been Printed & Saved as a final copy." & vbNewLine & vbNewLine & _
                    "If you wish to make changes, you should use a Revision.", vbExclamation, "Locked Sheet"
            End If
        End If
    End Sub


    Cell A1 has its value changed to "1" as a part of the initial print & save routine... so once saved, the user is reminded to use a Revision instead of editing this sheet.

    I have a sheet that is "Protected", with certain cells Unprotected to allow for user input.
    How can I use VBA to change this to have the sheet protected in its entirely (all cells protected)?


    Or at least provide a popup message if an unprotected cell is selected.


    TiA,

    Re: Run-time Error '13' Type Mismatch


    Quote from dotchiejack;796274

    Hello,
    Try to check the code with F8 key without On Error Resume Next in the code.


    Since last (the insertion of "On Error Resume Next"), more data has been added.
    Now it is working fine with or without "On Error Resume Next".
    The mysteries of VBA ;)

    Re: Run-time Error '13' Type Mismatch


    Quote from rory;796269

    That is, IMO, never a good option unless you know why the OERN is needed. You're not fixing the problem, just ignoring it, and it will probably bite you at some point. ;)


    I totally agree... that's why I'm going to Rem it out in a few days and see if the error mysterious goes away like it mysterious appeared.


    Thanks again for your time, Rory.

    Re: Run-time Error '13' Type Mismatch


    That had initially been the case when the workbook was first used, so I had dummy data in the first two lines of both L and U.
    At this point there is 5 data entries in L (4 unique), and 3 data entries in U (2 unique), with a total of 5 unique data entries combined.


    What gets me is, it had been working, but then stopped.
    I inserted an "On Error Resume Next" and it now works fine again... I'll Rem this line out later and see if the error returns or not.