none
Worksheet_SelectionChange Problems!! RRS feed

  • Question

  • I have developed the below listed code to prevent users from making accidental changes to a worksheet we use to produce Production Work Orders in a manufacturing setting.  The worksheet is password protected, but sometimes we must allow the user to unprotect to make simple formatting changes.  The problem is when unprotected, users tend to make inadvertent changes which they do not know how to fix.  The goal is to have the worksheet automatically re-protect itself when a "restricted" cell is chosen.  The code I have works fine for a simple single cell, but I need to expand this code to work for multiple ranges or cells, some of which may be merged, ($E$17), ($J$63) etc, etc.  for example.  Can someone help me with this??:

    ' Code to lock a Restricted Cell when selected
    ' When a restricted cell is selected the Sheet will Protect itself
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

        
         If Target.Count = 1 And Target.Row = 47 And Target.Column = 1 Then
          Sheets("WORK ORDER").Select
          ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
          , AllowInsertingHyperlinks:=True
    'A message box will notify the user or restriction
             MsgBox "RESPECT MY AUTHORITY!!"
       
         End If

    End Sub

    Thursday, February 7, 2013 5:22 PM

Answers

  • Instead of

        Set objWs = objWb.Worksheets(1)

    use

        Set objWs = objWb.Worksheets(5)

    if you want to refer to the 5th worksheet in the workbook, or

        Set objWs = objWb.Worksheets("MySheet")

    if you want to refer to the worksheet named MySheet.


    Regards, Hans Vogelaar

    • Marked as answer by jswan1001 Wednesday, March 6, 2013 10:15 PM
    • Unmarked as answer by jswan1001 Wednesday, March 6, 2013 10:16 PM
    • Marked as answer by jswan1001 Wednesday, March 6, 2013 10:17 PM
    Wednesday, March 6, 2013 9:46 PM
  • Change the lines

        ' Display Save As dialog
        objXl.Dialogs(5).Show ' 5 = xlDialogSaveAs

    to

        ' Save the workbook
        objWb.SaveAs FileName:=..., FileFormat:=51

    Replace the ... with the path and filename that you want; this can be an expression concatenating fixed parts and references to controls or fields.

    The 51 specifies that the workbook will be saved as an Excel 2007-2013 workbook.

    Example:

        objWb.SaveAs FileName:="C:\Excel\" & Me.IDField & ".xlsx", FileFormat:=51


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by jswan1001 Thursday, February 27, 2014 6:04 PM
    Thursday, February 27, 2014 7:03 AM

All replies

  • Change the line

         If Target.Count = 1 And Target.Row = 47 And Target.Column = 1 Then

    to

         If Not Intersect(Range("A47,E17,J63"), Target) Is Nothing Then

    Expand the list as needed.

    Regards, Hans Vogelaar

    Thursday, February 7, 2013 5:36 PM
  • Thanks Hans, that seems to be working great!!
    Thursday, February 7, 2013 7:36 PM
  • In this same template my data entry people are looking for a macro to open a blank workbook with a single click to avoid the: File>Open>Folder>Folder>File type of procedure.

    I've got this simple macro to do so:

    Sub New_File()
    '
    ' New_File Macro
    ' Creates New Blank File
    '

    '
        ChDir "Z:\DASHBOARD\UNDER CONSTRUCTION"
        Workbooks.Open Filename:= _
            "Z:\DASHBOARD\UNDER CONSTRUCTION\PRODUCTION WORK ORDER TEMPLATE(MACRO).xlsm"
    End Sub

    The only problem is that because it is the same workbook file I get a message, "this file is already open, reopening will cause any changes to be discarded."  Clearly one could Save As and change the file name before runtime for it to work, but is there something I could do to change the file name at Macro Run time?  I am trying to make the procedure as simple as possible for the people who use it.

    Thanks,

    Wednesday, February 13, 2013 3:53 PM
  • To clarify it's not to open a blank workbook, but rather a new copy of the same workbook with the entry fields cleared.
    Wednesday, February 13, 2013 3:55 PM
  • Try

    Sub New_File()
    '
    ' New_File Macro
    ' Creates New Blank File
    '
        Workbooks.Add Template:= _
            "Z:\DASHBOARD\UNDER CONSTRUCTION\PRODUCTION WORK ORDER TEMPLATE(MACRO).xlsm"
    End Sub


    Regards, Hans Vogelaar

    Wednesday, February 13, 2013 4:04 PM
  • Thanks Hans that seems to be working, it opens a file with 1,2,3 attached to file name each time the code runs.  Although I am having a problem running the macro from a button I added to the Quick Access Toolbar.  I get a message "cannot run macro.  It may not be available in the workbook."  The thing is I know it's in the workbook because it runs fine from the View>Macros>Run Tab and also runs fine through the VBA Window.  Any idea why this might be?

    Thanks,

    Justin

    Wednesday, February 13, 2013 7:01 PM
  • I'd remove the button and create it anew.

    Regards, Hans Vogelaar

    Wednesday, February 13, 2013 8:56 PM
  • Hans, could you help me out with changes I would need to make to make this same macro work from an Access Database file?

    Thanks

    Justin

    Friday, March 1, 2013 6:12 PM
  • You can use Automation to control Excel from Access. But you'd have to decide what to do with the new workbook. Do you want to display it so that the user can edit it, or do you want to manipulate the workbook from Access?

    Regards, Hans Vogelaar

    Friday, March 1, 2013 8:40 PM
  • I am using an Access Form as a Work/Sales Order and I would like to be able to export a single record and have the information populate my shipping documents in an Excel Workbook/Template.  It would not need to be manipulated from Access.

    Tuesday, March 5, 2013 6:16 PM
  • Can you tell us in more detail what you want to do? "export a single record and have the information populate my shipping documents in an Excel Workbook/Template" is a bit vague.


    Regards, Hans Vogelaar

    Tuesday, March 5, 2013 8:37 PM
  • I am using Access for Production Work Orders, which travel around our shop with a particular order.  This form contains information such as PO#, Material, Dimensions, Specification#'s, Mechanical Properties, Testing Results etc.  Much of this information is also required to be on our shipping documents, which include Material Cerification and Packing List form in Excel.  I just recently linked my Work Orders Table from Access, with my Material Cert and Packing Slip Workbook and can choose my Work Order number from the linked table and paste right into the Cert and Packing List using named fields.  I really like how this works, but my Excel files are now 10x larger.  Is there an easier or better way to get the information from my Access Work Orders Table to my Material Cert and Packing List Workbook?  Hope this clarifies.

    Thanks, Justin

    Tuesday, March 5, 2013 9:14 PM
  • The following is just to give you an idea. Let's say you place a command button cmdExport on the form, with the following On Click event procedure:

    Private Sub cmdExport_Click()
        Const strTemplate = "Z:\DASHBOARD\UNDER CONSTRUCTION\PRODUCTION WORK ORDER TEMPLATE(MACRO).xlsm"
        Dim objXl As Object
        Dim objWb As Object
        Dim objWs As Object
        On Error GoTo ErrHandler
        ' Start Excel
        Set objXl = CreateObject("Excel.Application")
        ' Create new workbook based on template
        Set objWb = objXl.Workbooks.Add(Template:=strTemplate)
        ' Refer to the worksheet
        Set objWs = objWb.Worksheets(1)
        With objWs
            ' Populate sheet, e.g.
            .Range("A1") = Me.ThisControl
            .Range("C1") = Me.ThatControl
            ' etc.
        End With
        ' Display Save As dialog
        objXl.Dialogs(5).Show ' 5 = xlDialogSaveAs
    ExitHandler:
        ' Clean up
        On Error Resume Next
        Set objWs = Nothing
        objWb.Close SaveChanges:=False
        Set objWb = Nothing
        objXl.Quit
        Set objXl = Nothing
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Sub

    You'll have to modify the code to suit your purpose.

    Regards, Hans Vogelaar

    Tuesday, March 5, 2013 9:36 PM
  • Thanks Hans, I've added a control button to my form, selected [event procedure] and entered the code into vba, although i've made no modifications, but for some reason I get absolutely no action when clicking the button, not even an error message.  I at least get an error message when entering the code under the default sub name: Private Sub Command393_Click().  Is it possible I've entered the code incorrectly or into the wrong place?

    Wednesday, March 6, 2013 4:07 PM
  • You should copy the code that I posted, without the Sub ... line and the End Sub line, into Private Sub Command393_Click().

    Next, modify the lines

            .Range("A1") = Me.ThisControl
           
    .Range("C1") = Me.ThatControl

    Change ThisControl and ThatControl to the names of controls (text boxes, combo boxes) on your form.


    Regards, Hans Vogelaar

    Wednesday, March 6, 2013 4:28 PM
  • Now we are getting somewhere Hans, I've made the recommended changes and am seeing some action from the button, but now i am getting an:  "Application-defined or object-defined error."  My current code is pasted below: please take a look.  Thanks, Justin


    Private Sub Command393_Click()
     Const strTemplate = "Z:\DASHBOARD\FORMS AND TEMPLATES\TEMPLATES\CERT AND PACKING LIST TEMPLATE.xltm"
        Dim objXl As Object
        Dim objWb As Object
        Dim objWs As Object
        On Error GoTo ErrHandler
        ' Start Excel
        Set objXl = CreateObject("Excel.Application")
        ' Create new workbook based on template
        Set objWb = objXl.Workbooks.Add(Template:=strTemplate)
        ' Refer to the worksheet
        Set objWs = objWb.Worksheets(1)
        With objWs
            ' Populate sheet, e.g.
            .Range("A1") = Command393
            .Range("C1") = Command393
            ' etc.
        End With
        ' Display Save As dialog
        objXl.Dialogs(5).Show ' 5 = xlDialogSaveAs
    ExitHandler:
        ' Clean up
        On Error Resume Next
        Set objWs = Nothing
        objWb.Close SaveChanges:=False
        Set objWb = Nothing
        objXl.Quit
        Set objXl = Nothing
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler


    End Sub

    Wednesday, March 6, 2013 4:59 PM
  • Command393 is a command button; command buttons don't have a value, so the lines

            .Range("A1") = Command393
            .Range("C1") = Command393

    are not meaningful. I assume that you want to populate some cells on the worksheet with the values of controls/fields on your form, but only you know what the names of those controls/fields are...


    Regards, Hans Vogelaar

    Wednesday, March 6, 2013 5:18 PM
  • ok, so i need to change the 'command393' to the field i wish to populate the cell with and the range obviously needs to be the range where i want the field to appear in the worksheet?  Sounds simple enough.  Thanks.

    Wednesday, March 6, 2013 6:06 PM
  • Yes, indeed. Suppose you want the LastName field in cell D4 and the FirstName field in cell G6. You'd use

             .Range("D4") = Me.LastName
             .Range("G6") = Me.FirstName

    You can add more such lines of course. If the field/control names contain spaces or punctuation, enclose them in square brackets, for example

            .Range("B5") = Me.[Employee Number]


    Regards, Hans Vogelaar

    Wednesday, March 6, 2013 6:18 PM
  • Very good.  So I've gotten to the point where the button opens the file to the Save As dialog box which allows me to save the file name and type.  But when I open the file afterward I see the information is not appearing in the worksheet.  Any ideas, current code is pasted below:

    Option Compare Database


    Private Sub Command393_Click()
     Const strTemplate = "Z:\DASHBOARD\FORMS AND TEMPLATES\TEMPLATES\CERT AND PACKING LIST.xltm"
        Dim objXl As Object
        Dim objWb As Object
        Dim objWs As Object
        On Error GoTo ErrHandler
        ' Start Excel
        Set objXl = CreateObject("Excel.Application")
        ' Create new workbook based on template
        Set objWb = objXl.Workbooks.Add(Template:=strTemplate)
        ' Refer to the worksheet
        Set objWs = objWb.Worksheets(1)
        With objWs
            ' Populate sheet, e.g.
            .Range("A1") = CUSTOMER
            .Range("B1") = our#
            .Range("C1") = SPEC
            ' etc.
        End With
        ' Display Save As dialog
        objXl.Dialogs(5).Show ' 5 = xlDialogSaveAs
    ExitHandler:
        ' Clean up
        On Error Resume Next
        Set objWs = Nothing
        objWb.Close SaveChanges:=False
        Set objWb = Nothing
        objXl.Quit
        Set objXl = Nothing
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler


    End Sub

    Wednesday, March 6, 2013 6:39 PM
  • As posted, the code should display an error message: the line

            .Range("B1") = our#

    is not valid because of the #. It should be

            .Range("B1") = [our#]

    Apart from that, it should work OK. I tested it in one of my databases, and the resulting worksheet was filled with the correct data.


    Regards, Hans Vogelaar

    Wednesday, March 6, 2013 8:16 PM
  • Hmmm.  I still cannot get data to appear in the workbook.  Could it be that I am not referencing the Worksheet properly?  Otherwise everthing is working great.

    Thanks, Justin.

    Wednesday, March 6, 2013 9:01 PM
  • Alas I've finally had some success, but in a Workbook with multiple sheets, how can I go about specifying a particular sheet?  Thanks again, Justin.

    Wednesday, March 6, 2013 9:10 PM
  • Instead of

        Set objWs = objWb.Worksheets(1)

    use

        Set objWs = objWb.Worksheets(5)

    if you want to refer to the 5th worksheet in the workbook, or

        Set objWs = objWb.Worksheets("MySheet")

    if you want to refer to the worksheet named MySheet.


    Regards, Hans Vogelaar

    • Marked as answer by jswan1001 Wednesday, March 6, 2013 10:15 PM
    • Unmarked as answer by jswan1001 Wednesday, March 6, 2013 10:16 PM
    • Marked as answer by jswan1001 Wednesday, March 6, 2013 10:17 PM
    Wednesday, March 6, 2013 9:46 PM
  • Very solid Hans, I don't know what I'd do without you!!  I think the problems I was having earlier getting the info into Excel were caused by some of the settings I had on the Workbook I was trying to use.  Everything seems OK now.  Thanks again, Justin
    Wednesday, March 6, 2013 10:19 PM
  • So I've got all the code in for my desired fields and the code seems to be solid.  But for some reason one of the fields I am exporting keeps showing up in excel as the number: 10800.  No matter the value is listed in the field, it keeps exporting as 10800.  I insert other fields into that line of code and it works just fine. Could there be a problem with the field or data type?  Data type is set to Text. The field displays width as part of a thickness/width dimensional product description.  The thickness field which has identical settings works just fine.
    Thursday, March 7, 2013 1:47 AM
  • Click anywhere in the line

    Private Sub Command393_Click()

    and press F9 to set a breakpoint.

    Switch to Access and click the button. The code will pause at the above line (it will be highlighted).

    You can single-step through the code by pressing F8 repeatedly.

    Hovering the mouse pointer over a variable will show its value in a tooltip.

    Perhaps this will provide a clue.

    If you've had enough of it, press F5 to run the code to completion.

    Click in the Sub ... line and press F9 to cancel the breakpoint.


    Regards, Hans Vogelaar

    Thursday, March 7, 2013 7:00 AM
  • I stepped through the code and the result of all fields was being displayed in quotation marks except for the 'Width' field which simply displayed 10800 with no quotes.  I wonder if the field name 'width' was causing some kind of confusion within Access, referring to field size or something like that, because I simply added an underscore to the end of the field name and it seems to be working just fine.  Thanks.
    Thursday, March 7, 2013 6:24 PM
  • Great. Without the underscore, it returned the width of the form (in twips, where 1 inch = 1440 twips).

    Regards, Hans Vogelaar

    Thursday, March 7, 2013 9:11 PM
  • Makes sense, 7.5 inches is the form width.  The Code Window appears once in a while when the button is pushed, how can I prevent that?  Also, I would like to add a Price/Lb field to the form/table that would be searchable through queries, but not visible when printed for the production team, how would you recommend doing that?

    Thanks,

    Justin

    Tuesday, March 12, 2013 2:52 PM
  • I may have spoke too soon on the new field, I should be able to hide it for "Print" under the properties tab
    Tuesday, March 12, 2013 2:58 PM
  • I'd design a report for printing purposes. The report would use the same record source as the form, but omit the Price/Lb field.


    Regards, Hans Vogelaar

    Tuesday, March 12, 2013 3:11 PM
  • Hans, I am trying to add a couple of controls to my form and I'm not sure if I need a combo box, list boxes or toggles.  For any given order there are two basic conditions: open or closed.  The only condition for a closed order is: shipped.  But any open order can have multiple conditions: on the production floor or awaiting raw material.  Awaiting raw material has no additional conditions, but an order considered to be on the floor could be: out for testing, awaiting approval, qa problems, etc.

    This would allow us to run queries and know exactly where certain orders are

    What I would like to have is choose:

    1. Open?: yes or no. 

    If no=shipped. 

    If yes, the option would be: awaiting material or on the floor.

    Awaiting material=no further option.

    On the floor= out for testing, awaiting approval, qa problems, etc. 

    Hope that makes sense, I'd like to have the options based on the previous choice.

    Thanks,

    Justin

    Wednesday, March 13, 2013 9:26 PM
  • You could use a check box or an option group with two option buttons for the Open/Closed choice

    Then an option group with three option buttons for Awaiting material/On the floor/Shipped.

    Finally - depending on the number of choices - an option group, list box or combo box for the choices for On the floor.

    In the After Update event of the Open/Closed check box or option group:

    If Closed, select Shipped in the second option group and lock or disable it.

    If Open, enable or unlock the second option group, but disable Shipped.

    In the After Update event of the second option group, enable or unlock the third option group/list box/combo box only if On the floor has been selected.


    Regards, Hans Vogelaar

    Wednesday, March 13, 2013 9:56 PM
  • So I would use the Expression Builder and not Code to make this possible?

    Wednesday, March 13, 2013 10:55 PM
  • You'd use code in the After Update events: select [Event Procedure] from the dropdown list in the event, then click the builder dots ... to the right of the dropdown arrow.

    Regards, Hans Vogelaar

    Wednesday, March 13, 2013 10:57 PM
  • So would you recommend an "if...then" type statement or a "select case" type of statement because of the multiple conditions?

    Thursday, March 14, 2013 1:21 AM
  • Yes, indeed.

    Regards, Hans Vogelaar

    Thursday, March 14, 2013 6:54 AM
  • I've decided to go with a check box for [Open]  And two list boxes for [Status] and [Disposition].  I'd like the check box to control what values are available in the list boxes.  Status being on_the_floor or "otf", awaiting_raw_material or "arm" and complete or "comp".  Disposition being "lab" "heat treat" "qa" or "approval" I'm not exactly what event I want to use or how to write code but below is pasted what I've got so far.  Please take a look if you could:  Thanks, Justin.

    Private Sub OPEN_AfterUpdate()
        ' set variables
        Dim boolOpen As Boolean
        Dim strDisposition As String
        Dim strStatus As String
        On Error GoTo ErrHandler
        'if statements
        If boolOpen = False Then
        strStatus = "comp"
        strDisposition = "null"
        End If
        If boolOpen = True Then
        strStatus = "otf" Or "arm"
        strDisposition = "lab" Or "HEAT TREAT" Or "QA" Or "APPROVAL"
        End If

    End Sub

    Thursday, March 14, 2013 2:58 PM
  • A list box can be populated in two ways (basically):

    • If the Row Source Type property is set to Table/Query, the list box is populated by specifying the name of a table or query or a SQL statement in the Row Source property.
    • If the Row Source Type property is set to Value List, the list box can be populated by specifying a semicolon-separated list of values in the Row Source property, or by using the AddItem method in Visual Basic.

    You could use the second option here, since the number of items is very small (generally, the first option is more flexible).

    I'd populate the Status list box from the After Update event of the Open check box, then populate the Disposition list box from the After Update event of the Status list box.

    Private Sub Open_AfterUpdate()
        With Me.Status
            .RowSource = ""
            If Me.Open = False Then
                .AddItem "Complete"
                .Enabled = False
            Else
                .AddItem "On the floor"
                .AddItem "Awaiting raw material"
                .Enabled = True
            End If
        With Me.Disposition
            .RowSource = ""
            .Enabled = False
        End With
    End Sub
    
    Private Sub Status_AfterUpdate()
        With Me.Disposition
            .RowSource = ""
            If Me.Status = "On the floor" Then
                .AddItem "Lab"
                .AddItem "Heat treatment"
                .AddItem "QA"
                .AddItem "Approval"
                .Enabled = True
            Else
                .Enabled = False
            End If
        End With
    End Sub

    (Warning: air code, I didn't test it)

    Regards, Hans Vogelaar

    Thursday, March 14, 2013 5:05 PM
  • Forgive me Hans, but this is a little complex for me so I'd like to go through, 1 sub (section) at a time. I have already generated my 2 list boxes Status and Disposition with Row Source type properties.  The list for Disposition being: "LAB";"HEAT TREAT";"QA";"APPROVAL" and the list for Status being: "ARM";"OTF";"COMP".  So for the first sub I should replace With Me.Status with just the field name Status? And I am not really quite sure what needs to go between the quotes in   .RowSource = ""  should that be a field name?  And I have the same question for:   With Me.Disposition
                                                                                                   
    .RowSource = ""

    I think if I can these figured out that should help me on my way to figuring out both subs.  Thanks, as always

    Justin

    Thursday, March 14, 2013 7:34 PM
  • Assuming that the list box is named Status, you should leave the line

        With Me.Status

    unchanged. Me refers to the form running the code.

    There shouldn't be anything between the quotes in the line

            .RowSource = ""

    This line clears the list box.


    Regards, Hans Vogelaar

    Thursday, March 14, 2013 8:22 PM
  • Very nice Hans, that got everything working pretty good, but I think I still have couple bugs to work out.  Everything works when I first open the form, but when I check the Box, the Disposition box is disabled and does not re-enable, I need that to work when the Box is checked.  I'm not sure if it's something in the code or if I have a property set wrong.  Could you take a look at my current code and let me know what you think?  Thanks, Justin

     

    Private Sub Open_AfterUpdate()
        With Me.STATUS
            .RowSource = ""
            If Me.OPEN = False Then
                .AddItem "Complete"
                .Enabled = False
            Else
                .AddItem "On the floor"
                .AddItem "Awaiting raw material"
                .Enabled = True
            End If
        With Me.DISPOSITION
            .RowSource = ""
            .Enabled = False
        End With
        End With
    End Sub

    Private Sub Status_AfterUpdate()
        With Me.DISPOSITION
            .RowSource = ""
            If Me.STATUS = "On the floor" Then
                .AddItem "Lab"
                .AddItem "Heat treatment"
                .AddItem "QA"
                .AddItem "Approval"
                .Enabled = True
            Else
                .Enabled = False
            End If
        End With
    End Sub
    End Sub

    Friday, March 15, 2013 2:35 PM
  • There was an error in my original code - I forgot one "End With". I see that you added it, but it's not quite in the right place. Here is the corrected version:

    Private Sub Open_AfterUpdate()
        With Me.Status
            .RowSource = ""
            If Me.Open = False Then
                .AddItem "Complete"
                .Enabled = False
            Else
                .AddItem "On the floor"
                .AddItem "Awaiting raw material"
                .Enabled = True
            End If
        End With
        With Me.Disposition
            .RowSource = ""
            .Enabled = False
        End With
    End Sub
    
    Private Sub Status_AfterUpdate()
        With Me.Disposition
            .RowSource = ""
            If Me.Status = "On the floor" Then
                .AddItem "Lab"
                .AddItem "Heat treatment"
                .AddItem "QA"
                .AddItem "Approval"
                .Enabled = True
            Else
                .Enabled = False
            End If
        End With
    End Sub

    Sorry about the mistake!

    Regards, Hans Vogelaar

    Friday, March 15, 2013 3:44 PM
  • No need to apologize Hans, you've already helped me a great deal.  I still cannot get the Disposition List Box to function after checking and unchecking the Open Box.  How can I adjust the code to make this function properly?

    Thanks,

    Justin

    Tuesday, March 19, 2013 6:55 PM
  • I have no idea why it doesn't work for you. I have uploaded a sample database to Dropbox: https://www.dropbox.com/s/ms9hyncukfj798b/JSwan.mdb

    Please study the design of the very simple form in the database and the code behind it.


    Regards, Hans Vogelaar

    Tuesday, March 19, 2013 8:48 PM
  • Thanks Hans I was able to work through my problems.  But I do have another simple (or so I think) project I hope you can help me with.  I have a query called 'OnTheFloor' which I have linked to an Excel workbook.  The idea is to have the table accessible, viewable and manipulatable, without changing any of the data in Access.  The only problem is that I'd like to change so of the Format in Excel for appearence and printability purposes.  Unfortunately my formatting my formatting changes back each time I refresh the Data Connection.  What I would like to do is add a simple userform with a command button that will format cells and display options etc on the 'click' event, I'm just not exactly sure of how to get my code started.  Could you help me out please?

    Thanks,

    Justin

    Monday, March 25, 2013 3:34 PM
  • Can you provide more detailed information? What is the range you want to format, and how do you want to format it?

    Regards, Hans Vogelaar

    Monday, March 25, 2013 3:59 PM
  • I am currently using 10 columns A:J respectively.  For now the only formatting that I believe I need to change would be column width be @ 1" or 1.5" depending.
    Monday, March 25, 2013 4:09 PM
  • Column width in Excel is not specified in inches or points, but in characters, so setting the width in inches is a bit of work. Here is an example:

    Sub SetWidth(col As Long, inches As Double)
        Dim dblWidth As Double
        Dim i As Long
        dblWidth = Application.InchesToPoints(inches)
        For i = 1 To 4
            With Columns(col)
                .ColumnWidth = .ColumnWidth * dblWidth / .Width
            End With
        Next i
    End Sub
    
    Sub SetWidths()
        Application.ScreenUpdating = False
        SetWidth 1, 1.5
        SetWidth 2, 1.5
        SetWidth 3, 1
        SetWidth 4, 1.5
        SetWidth 5, 1
        SetWidth 6, 1
        SetWidth 7, 1.5
        SetWidth 8, 1
        SetWidth 9, 1
        SetWidth 10, 1.5
        Application.ScreenUpdating = True
    End Sub

    SetWidths is the macro to modify and to run.

    Regards, Hans Vogelaar

    Monday, March 25, 2013 4:41 PM
  • Thank you Hans.  Using the macro recorder and setting the columns to my desired widths I am given:

    If I use these values in the SetWidths Sub I will not need the top portion you've listed?

    Sub Macro1()
    '
    ' Macro1 Macro
    '   Columns("A:A").ColumnWidth = 16.43
        Columns("C:C").ColumnWidth = 16.43
        Columns("D:D").ColumnWidth = 16.43
        Columns("E:E").ColumnWidth = 13
        Columns("F:F").ColumnWidth = 16.43
        Columns("G:G").ColumnWidth = 13
        Columns("H:H").ColumnWidth = 13
    End Sub

    Monday, March 25, 2013 4:54 PM
  • With this macro, you won't need the code that I posted.

    Regards, Hans Vogelaar

    Monday, March 25, 2013 7:54 PM
  • Works great, I am trying to learn to write my own code, but sometimes using the recorder just makes sense.  I am building a new quality database to help track non-conformances and rejections.  But when I'd like to have an Excel Worksheet to send to customers and suppliers, so I am trying to use the same code we put together to export data from a form.  It's the exact same situation as previous, but for some reason I am getting the message: 'Access cannot find the field 'l1' referred to in your expression.'  I've checked all field names, but I am not sure what's wrong, could you take a look? Thanks, Justin.

    Private Sub Command121_Click()
    Const strTemplate = "Z:\DASHBOARD\FORMS AND TEMPLATES\TEMPLATES\cert and packing list.xltm"
        Dim objXl As Object
        Dim objWb As Object
        Dim objWs As Object
        On Error GoTo ErrHandler
        ' Start Excel
        Set objXl = CreateObject("Excel.Application")
        ' Create new workbook based on template
        Set objWb = objXl.Workbooks.Add(Template:=strTemplate)
        ' Refer to the worksheet
        Set objWs = objWb.Worksheets(2)
        With objWs
            ' Populate sheet, e.g.
            .RANGE("A1") = [VENDOR:]
            .RANGE("A2") = PO#
            .RANGE("A3") = SIZE
            .RANGE("A4") = ALLOY
            .RANGE("A5") = [QTY ORDERED]
            .RANGE("A6") = [QTY RECEIVED]
            .RANGE("A7") = [DATE RECEIVED]
            .RANGE("A8") = UWC#
            .RANGE("A9") = [QTY REJECTED]
            .RANGE("A10") = COMPLAINT
            .RANGE("A11") = [SAMPLES SENT?]
            .RANGE("A12") = [PICS SENT?]
            .RANGE("A13") = [USE AS IS?]
            .RANGE("A14") = SCRAP
            .RANGE("A15") = OTHER
            .RANGE("A16") = [REWORK?]
            .RANGE("A17") = [CREDIT?]
            .RANGE("A18") = [CORRECTIVE ACTION?]
            .RANGE("A19") = [CLOSED?]
            .RANGE("A20") = [DATE CLOSED]
           
                  ' etc.
        End With
        ' Display Save As dialog
        objXl.Dialogs(5).Show ' 5 = xlDialogSaveAs
    ExitHandler:
        ' Clean up
        On Error Resume Next
        Set objWs = Nothing
        objWb.Close SaveChanges:=False
        Set objWb = Nothing
        objXl.Quit
        Set objXl = Nothing
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler

    End Sub

    Wednesday, March 27, 2013 1:32 AM
  • In addition to the field names containing spaces and question marks, you should enclose those containing # in square brackets:

    [PO#]

    [UWC#]


    Regards, Hans Vogelaar

    Wednesday, March 27, 2013 6:54 AM
  • I've tried bracketing just about everything, fields with symbols and spaces and even fields without spaces, but it keeps happening.  When I set a breakpoint at the top and step thru the code, the message doesn't appear with the 'populate sheet portion, where the fields are listed, but after all code has run. F8 button takes me from:

    .Range("A1") to

    ErrHandler:
        MsgBox Err.Description, vbExclamation.

    The Code finishes, the message appears and the VBA window Highlights the last line of code, 'Resume ExitHandler.

    So I feel like maybe it's not my fields but something else?

    Justin

    Wednesday, March 27, 2013 1:37 PM
  • If you single-step through the code with F8, what do you see when you hover the mouse pointer over [VENDOR:] ?

    Or if you activate the Immediate window (Ctrl+G) while the code is paused, type

    ? [VENDOR:]

    and press Enter?


    Regards, Hans Vogelaar

    Wednesday, March 27, 2013 2:14 PM
  • That did it Hans!  Immediate window was returning the same message, this time giving me run-time error '2465.'  What happened is I have a table for customer complaints and one for supplier complaints, both have very similar fields, but I had the wrong control source set up for my form.  Looks like it's going to work great now.

    Thanks,

    Justin

    Wednesday, March 27, 2013 2:51 PM
  • Hans, when exporting check box data the values come through as 0 or -1, is there anyway to change that to Y/N in Access Code or what would be the easiest way to do so in Excel?

    Wednesday, March 27, 2013 3:33 PM
  • Let's say SAMPLES SENT? corresponds to a check box. You can then use

            .RANGE("A11") = Format([SAMPLES SENT?], """No"";""Yes""")


    Regards, Hans Vogelaar

    Wednesday, March 27, 2013 3:45 PM
  • Thanks Hans.  I've added an Attachments field to my form, I was wondering if you could help me write a procedure that will print any attachments when the field is  Double-Clicked.??

    Thanks,

    Justin

     
    Friday, April 12, 2013 4:07 PM
  • Sorry, I never use attachment fields in my databases; I can't help with that. I'd start a new thread for this problem.

    Regards, Hans Vogelaar

    Friday, April 12, 2013 4:55 PM
  • Perhaps you can help me out with this one...Recently I did a re-build of a database, before doing so I was sure to back it up.  Incidentally I deleted what eventually turned out to be a pretty important field.  How can I import a single field from my back-up file into my current database file.  Using the autonumber feature I am sure the info should match up.  Can you help me?
    Thursday, June 6, 2013 1:16 AM
  • Open the 'current' database. If you really deleted the field from the relevant table, recreate it. It will be empty, of course. If you merely cleared the field instead of deleting it, you won't have to recreate it of course.

    Use External Data > Access > Link to create a link to the corresponding table in the 'backup' database. Access will automatically give it a different name than the original one.

    Create a new query in design view and add both the relevant table and its counterpart. Join them on the AutoNumber field.

    Change the query type to Update.

    Add the field from the relevant table (i.e. the table that 'lives' in the current database) to the query grid.

    In the 'Update to' row, enter [NameOfLinkedTable].[NameOfField] with the appropriate names substituted, of course.

    Run the query.


    Regards, Hans Vogelaar

    Saturday, June 15, 2013 8:49 PM
  • Thanks, Hans.

    I could use a hand on something else I am working on...I am working on a scheduling app in Excel, using Active X combo boxes whose Lists are filled with info I am importing from an Access 'Open Work Orders Query'.  The Combo Boxes will display the jobs in order which are to be run on a particular machine...My problem now is updating, I would like to develop a macro that will move the Orders up to the next box as a job is completed, so that my Production Manager does not have to click and move each job up manually, which seems very time consuming.  Any recommendations??

    Monday, July 8, 2013 4:12 PM
  • Could you start a new thread for this? Your new question is not related to the current thread.

    Please provide detailed information about the combo boxes - it's not clear from your description what you mean by the "next box".

    Thanks in advance!


    Regards, Hans Vogelaar

    Monday, July 8, 2013 4:56 PM
  • Thanks Hans, I've started a new thread at:

    Active X Controls and Macros in Excel-Microsoft Office for Developers Forums-Excel for Developers                
    Excel for Developers
    This forum is for Developer discussions and questions involving Microsoft Excel
    Announcements : 0
         
    Monday, July 8, 2013 7:27 PM
  • As time has gone by this is working great, but I am trying to refine everything for my users.  Would it be possible to modify this code to specify what folder it should save to?  Right now the Save As dialog  box comes up and the user chooses the folder, I would like the user not have to do that because a Packing List will always be saved in the Packing List Folder.  

    Thanks Again,

    JS

    Thursday, February 20, 2014 6:29 PM
  • Do you want a fixed file name too, or should the user be able to specify the file name?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, February 20, 2014 8:23 PM
  • It would be great it the file could be named using Primary Key fields.

    Thanks,

    JS

    Thursday, February 27, 2014 3:31 AM
  • Change the lines

        ' Display Save As dialog
        objXl.Dialogs(5).Show ' 5 = xlDialogSaveAs

    to

        ' Save the workbook
        objWb.SaveAs FileName:=..., FileFormat:=51

    Replace the ... with the path and filename that you want; this can be an expression concatenating fixed parts and references to controls or fields.

    The 51 specifies that the workbook will be saved as an Excel 2007-2013 workbook.

    Example:

        objWb.SaveAs FileName:="C:\Excel\" & Me.IDField & ".xlsx", FileFormat:=51


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by jswan1001 Thursday, February 27, 2014 6:04 PM
    Thursday, February 27, 2014 7:03 AM
  • Excellent Hans, that took about 2 minutes to change!...Now, in a few of my forms I am using ExportWithFormatting Macros to create .PDF's from corresponding Reports; I have the same problem with the user having to name and select locations for the files.  Could the same type of adjustment be made to a Macro or would it first need to be changed to VBA code?

    Thanks, 

    JS

    Thursday, February 27, 2014 6:04 PM
  • I hardly use macros in Access. I think it should be possible to do something similar.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, February 27, 2014 8:26 PM
  • I would like to get away from Macros and start using VBA, I hear macros are a dying breed.  If I were to write code to accomplish this I would want to use a DoCmd Function?  I am doing some research and it looks like I need something like:

    DoCmd.OutputTo acOutputReport, ''Purchase Orders'', acFormatPDF, "PO_[poID]",True

    I would like this to fire from an OnClick event from the Purchase Orders and export the current record.  

    Am I on the right track here?

    Thanks,

    JS

    Thursday, February 27, 2014 10:38 PM
  • No, macros aren't dying. If you want to create a web database, you cannot do without them - macros work in a web database, VBA doesn't.

    VBA code to export a report could look like this:

    DoCmd.OutputTo ObjectType:=acOutputReport, ObjectName:="Purchase Orders", OutputFormat:=acFormatPDF, OutputFile:="C:\Export\" & Me.PO_ID & ".pdf"

    Note that the reference to the control/field PO_ID is outside the quotes.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, February 28, 2014 10:12 AM
  • This works great Hans, but one more thing; how can I set the Excel Workbook Sub to autostart after saving the workbook so the user can review the information?

    Regards,

    JS

    Wednesday, March 5, 2014 1:54 AM
  • If you want to display the workbook, change

        objWb.SaveAs FileName:=..., FileFormat:=51
    ExitHandler:
         ' Clean up
         On Error Resume Next
         Set objWs = Nothing
         objWb.Close SaveChanges:=False
         Set objWb = Nothing
         objXl.Quit
         Set objXl = Nothing
         Exit Sub
    ErrHandler:
         MsgBox Err.Description, vbExclamation
         Resume ExitHandler
    End Sub

    to

        objWb.SaveAs FileName:=..., FileFormat:=51
        objXL.Visible = True
    ExitHandler:
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Sub
    


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, March 5, 2014 7:13 AM
  • Excellent Hans, thank you.  I spoke too soon when I said just one more thing...Sometimes we need to make partial shipments and therefore will have more than 1 Packing List/Shipping Paperwork.  Right now when I run this Sub if the file already exists Windows asks if it should replace the existing file, which is fine if I am just trying to update information, but not if we need mulitple Packing Lists.  Is there a way to have the option to replace the existing file or save it with the same name and a suffix (ex: 10205 RENTON COIL and 10205 RENTON COIL (2) ).  I take it this would require an IF statement somewhere within the SaveAs line of code?

    Regards,

    js

    Wednesday, March 5, 2014 4:17 PM
  • Replace the line that saves the workbook with the following extended code:

        Dim strFile As String
        strFile = "..." ' assemble path+filename
        If Dir(strFile) <> "" Then
            ' file exists already
            strFile = Left(strFile, Len(strFile) - 5) & " (2).xlsx"
        End If
        objWb.SaveAs Filename:=strFile, FileFormat:=51


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, March 5, 2014 10:14 PM
  • Thank you Hans.  I've got something else I've been working on and need some help; I've started a new thread Output Report and Send Message w/ Attachment, but have not quite found what I need.  
    Basically, combining the two functions to Export a PDF, then Create an Editable Email with the PDF attached  I kind of understand the concept, but I'm not quite sure how to go about it.  I feel like I need to define the Report as an Object variable, so that the file can be passed on to the CreateMessage with an attachment?

    Thanks, 

    js

     
    Tuesday, March 11, 2014 2:48 PM