Results 1 to 7 of 7

Thread: [RESOLVED] VB6 Automation vs Manual in Excel

  1. #1

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Resolved [RESOLVED] VB6 Automation vs Manual in Excel

    Hi guys,

    I'm not sure this is doable, but I thought I'd ask anyway. Also, it's possibly more to do with VBA than VB6, but it's both really.

    Let's say I've got a CheckBox on an Excel Worksheet. Easy enough. I click it, and it might run some VBA code.

    Now, I also want to check it through VB6 automation. However, this time, I don't want it to run any VBA code (or, at least, not the same code).

    So, here's the question: From VBA, is there a way to know if Excel is being automated via VB6 versus just VBA macros running because of user interaction?

    I suppose I could reach into the VBA from VB6 and set a flag, but I'd rather not do that. Also, I suppose I could also have a second CheckBox tucked away on some Excel tab(worksheet) somewhere and use it as a flag (which I'll do unless there's a more nifty answer).

    Thanks,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: VB6 Automation vs Manual in Excel

    you should easily be able to use events to differentiate between user mouse or keyboard operation, from just vb6 operation by code
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: VB6 Automation vs Manual in Excel

    westconn1,

    I'm not sure I "get" what you're saying. Let me outline my situation a bit more. Here's a cropped screenshot of a certain Excel worksheet:

    Name:  cb.png
Views: 211
Size:  3.0 KB

    Now, when you click any of those checkboxes, certain VBA macro code runs. Specifically, if you click the one that says "Show Normals Bands", you get the gray bands on plots like the following:

    Name:  plot.png
Views: 213
Size:  12.0 KB

    There's a substantial chunk of code that creates those gray bands, and it's in a module that's perfectly shared between VB6 and that Excel file. (It's shared in that anytime changes are made to it, it's copy-pasted from VB6 into the Excel template file (XLTM).)

    Now, here's my problem. I generate new Excel plot files from VB6. And I also decide whether or not to show those gray bands from VB6, and I also draw them from VB6 if they're to be displayed. And I also check that checkbox in Excel if they're drawn. Here's the problem. I run into a bit of a recursion-like issue. When I check the Excel checkbox through VB6 automation, Excel runs it's "draw gray bands" code, but I'm also executing it from VB6. I'd rather it just run once.

    In other words, I'd like to check-the-checkbox from VB6, and have Excel be smart enough to know that it doesn't need to run any code when that's happening. In other words, Excel needs to detect that it's being automated, and that it's not a user clicking the checkbox.

    Best Regards,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,043

    Re: VB6 Automation vs Manual in Excel

    how did you add the Checkbox, is it Fix?

    can you check/uncheck with...
    Code:
     Checkbox1 = IIf(someName, 1, 0)
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: VB6 Automation vs Manual in Excel

    Yeah, I went with the checkbox approach. Here, I'll show you what I did:

    Name:  auto.png
Views: 188
Size:  1.4 KB

    That's in Excel, on an obscure tab of the template that the users will never go to. This particular Excel template has about 20 tabs (worksheets).

    And then, in the VB6 code, I do something like this:

    Code:
    
        ' Some setup.
    
    
        Set wbk = ExcelAddWorkbook(xls, sTemplateSpec)
        xls.Calculation = xlCalculationManual
        xls.ScreenUpdating = False
        wbk.Worksheets("Utilities").Shapes("chkBeingAutomated").ControlFormat.Value = xlOn  ' This prevents "redundant code execution" in the Excel VBA macros.
    
    
    
        '
        ' Do ALL KINDS of stuff to the new Excel file.
        '
    
    
    
        xls.Calculation = xlCalculationAutomatic
        XlsPlotRecalculateEverything xls, wbk
        xls.ScreenUpdating = True
        '
        Set wsh = wbk.Worksheets(1)
        wsh.Select
        '
        wbk.Worksheets("Utilities").Shapes("chkBeingAutomated").ControlFormat.Value = xlOff ' And turn it off again so the Excel file will work for the user.
        ExcelSaveAs wbk, sFileSpec, False
    
    
        ' Continue with cleanup.
    
    
    And then, in a couple of the Excel macros, I have code like this:

    Code:
    
    Public Sub chkShowOptionalKinematicPlots_Click()
        Dim bShowOptional As Boolean
        Dim bShowFootRotation As Boolean
        '
        If ThisWorkbook.Worksheets("Utilities").Shapes("chkBeingAutomated").ControlFormat.Value = xlOn Then Exit Sub
    
    	...
    
    This totally takes care of my problem. It's not totally clean, but it works. It'd be nice if there was some ThisWorkbook.BeingAutomated flag, but I don't think there is.

    I'll mark this as resolved.

    Elroy
    Last edited by Elroy; Feb 13th, 2018 at 03:18 PM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] VB6 Automation vs Manual in Excel

    I'm not sure I "get" what you're saying.
    you can use mouse and keyboard down events (etc), to know if a user has clicked the checkbox
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: [RESOLVED] VB6 Automation vs Manual in Excel

    Quote Originally Posted by westconn1 View Post
    you can use mouse and keyboard down events (etc), to know if a user has clicked the checkbox
    Ahhh, I see your point. Not the worst of ideas. Since I've actually got it going though, I'll probably stick with what I've got, unless someone comes up with some internal Excel flag I can use. I thought about using the .Visible property because I hide my copy of Excel when I'm automating. However, that also has certain problems for me, specifically, that I make it visible when I'm debugging certain automation functions (and watch what VB6 is doing, step-by-step).

    I just threw this out here because ... it's actually quite amazing how often I've thought, "hmmm, I bet there's no way to do this", and then someone in these forums totally nails it.

    All The Best,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

Posting Permissions

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



Click Here to Expand Forum to Full Width