Results 1 to 6 of 6

Thread: Cell update from a Link that needs to be ENTERED

  1. #1

    Cell update from a Link that needs to be ENTERED



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Good Day,
    First posting, have read and used by modification some of Ken's work. THANKS!!!!.I have learned quite a bit.
    I am having a very difficult time trying to use the update to Cell A1 (which is under the Private Sub Worksheet), this update comes from a machine. This Cell is supposed to run Module2.
    The Cell does update to a One(1) when the machine tells the Cell to update but no PrintToPDF_Late.
    If I manually enter a One(1) in cell A1 and push the Enter key the macro runs just fine.
    Since I am working with Machine logic all is a 1 or 0.
    I am using Excel 2003

    What have I missed ?
    All works (Thanks Ken ) except the update Cell A1.
    Thanks for all your help.
    Kevin
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Hi there, and welcome to the forum.

    Try replacing your Worksheet_Change event with the following:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Parent.Range("A1").Value = 1 Then
            Call PrintToPDF_Late
        End If
    End Sub
    Let me know if that does the trick.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hi Ken, Thanks for letting me be part of the Forum, and the FAST response.
    I plugged in your code, and the sheet still sits there. A One (1) does populate the cell as it has done.
    This is where it stops, I put a MsgBox in and it never gets there.
    When I remove the link in the cell, enter a One (1), and use the Enter Key all works Great.

    Kevin

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    I need to know a bit more about how you're driving the update. Is it just refreshing data from somewhere, or does the change only happen when you physically enter data in (any) cell?

    The Worksheet_Change routine only fires when you put new data into a cell. (For our purposes it would be close enough to say that pressing the Enter key on a cell is what triggers it.)

    If you're driving updates from somewhere else, you'll probably want to looka t the Worksheet_Calculate event... but that fires much more often...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    The whole worksheet is updated via RSLinx, Rockwell Automation software.
    The worksheet is a LIVE always-updating the sheet.
    The Machine has an operator interface that has a SPC Count Reset pushbutton.
    This pushbutton tag is the N15:15 reference in cell A1.
    The operator will push and hold the reset pushbutton for Five (5) seconds, this time is to allow the worksheet to run the appropriate Macro to save the Production data for that shift. All the machine will send to the A1 cell is a One (1) for run the Macro or a Zero (0).
    As you can see, It will E-Mail the PDF’s to people of choosing.

    The idea would be to trigger the Macro ‘PrintToPDF_Late’ when A1 = One (1)
    The Machine has no way of sending an ENTER KEY function.

    Thanks for your Time :>)

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Okay, try this and let me know if it works. I'm just a little leary that it may fire too often:

    Code:
    Private Sub Worksheet_Calculate()
        If Worksheets("MO1062").Range("A1").Value = 1 Then
            Call PrintToPDF_Late
        End If
    End Sub
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •