Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Disable all controls on a worksheet temporarily? (2000/sp3)

    (Edit: I couldn't find an answer in the search mechnism. If an answer already exists, please just point me to it. thanks!)

    I have an Excel worksheet with a lot of button and listbox controls on it (I'm basically using Excel as a glorified GUI since I don't have a VB compiler). When a particular button is pushed, the corresponding event takes quite some time (it is basically linearly processing a bunch of files that can take many minutes).

    During this time of processing, I basically want to disable the entire worksheet from responding to the user (button clicks, etc), however I still want to be able to update the controls' data (status fields) for the user to see that we're not locked up...

    What is the best way to do this? I have thought of the following mechanisms:

    - Go through some kind of internal "collection" object in VBA to set all "Enabled" values to "false".

    - Somehow set an "enabled" property for the worksheet (or workbook) to false

    - Create a dummy "label" or some other control. Bring it to the front when processing so that it "blocks" mouse clicks.

    - Individually hard code setting each control's "enabled" property to false

    The first solution is ideal, but what is the control collection that I can use? I can't find any in Excel help... The second solution is equally ideal, but again, cannot find anything in Excel help. The third and fourth options are viable, but extremely messy (especially if at some future date I (or someone else -- eek!) go and change/add/remove objects and forget about the "protection" algorithms)

    Any help is greatly appreciated!
    ..dane

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Disable all controls on a worksheet temporarily? (2000/sp3)

    Not sure what you are after.

    When a macro is running the workbook functionality is disabled. Esc or ctrl-break can be used but these can be disabled using the EnableCancelKey (see in Help) property of the application object.

    It can be set to "interrupt" the code (default), disable it completely, or send it to the error handler that you may have in the code.

    A simple way to give progress is to pass info into the statusbar (see in Help) property of the application object.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Disable all controls on a worksheet temporarily? (2000/sp3)

    You can do something like this:

    Sub SetControls(Flag As Boolean)
    Dim i As Integer
    For i = 1 To ActiveSheet.Shapes.Count
    ActiveSheet.Shapes(i).ControlFormat.Enabled = Flag
    Next i
    End Sub

    To disable controls:

    SetControls False

    and to enable them:

    SetControls True

  4. #4
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable all controls on a worksheet temporarily? (2000/sp3)

    Steve,

    Let me expand a little. I suppose there is one particular aspect of my macro that is causing the problem...

    I have a listbox that I populate with filenames as they are selected by the User. Once all the files have been populated, the user can select one/many/all files and hit the "process" button. This "process" event goes through the listbox entries sequentially and runs an external script (batch file in command shell) on each file, and updates their status as "waiting," "processing," "success," fail," etc.

    I am using some code I got off the 'net to both spawn the shell as well as wait for its completion (so as to not bog down the server with dozens of processing scripts at the same time)...

    here is a snippet of the code executing the script:

    ------------------------------------
    ' Execute our huge script and redirect the output to a temp file
    dblRetVal = ExecCmd3(strExecCommand)

    ' Check the return value and update the results column
    If dblRetVal > 0 Then
    lstProjectsSelected.List(lngIndex, 3) = "Failed"
    Else
    lstProjectsSelected.List(lngIndex, 3) = "Success"
    End If
    ------------------------------------

    and here is the ExecCmd module I got online:

    ------------------------------------
    Option Explicit

    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As _
    Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
    Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As _
    Long, lpExitCode As Long) As Long
    Const STILL_ACTIVE = &H103
    Const PROCESS_QUERY_INFORMATION = &H400


    Public Function ExecCmd3(cmdline$) As Integer
    Dim hTask As Long
    Dim hProcess As Long
    Dim exitCode As Long

    hTask = Shell(cmdline$, vbMinimizedNoFocus)
    hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hTask)

    ' loop until the process returns a valid exit code
    Do
    ' relinquish this CPU time slice
    Sleep 100
    DoEvents
    ' query for exit code
    GetExitCodeProcess hProcess, exitCode
    Loop While exitCode = STILL_ACTIVE

    ExecCmd3 = exitCode

    End Function
    ------------------------------------


    As you can see, the event is specifically designed to let Excel do other things while it is waiting on the script to finish. Since this process may take over ten minutes, I want this functionality (otherwise even the Window refresh functionality of the Excel application does not occur, and it looks as if the entire application has locked up even though it is just 'waiting').

    However, I specifically do not want any "events" to occur (button pushes, listbox selection changes, etc) on the worksheet's controls, since it will adversely affect the sequential processing done by the macro...

    Hope that helps clarify what I'm doing and why I'm doing it this way... If you have a better option, I'm all ears. But this is so far the _only_ way I've been able to both spawn external scripts as well as wait on them without adverse side-effects to the windows GUI subsystem.

    thanks,
    ..dane

  5. #5
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable all controls on a worksheet temporarily? (2000/sp3)

    Hans,

    This is exactly what I was looking to do, but did not know the name of the collection of which they would be a part.

    Thanks so much. Working great now.... no more user-caused errors! (only programming errors now!)

    ..dane

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Disable all controls on a worksheet temporarily? (2000/sp3)

    Hans,
    Could you explain your code please? I would like to know what this is doing. I see you refer to the shapes collection. What has this got to do with worksheet controls?
    I understood from Dane's post that he wanted all controls (check boxes, option buttons, command buttons, etc) to be disabled or enabled and leave the rest of the spreadsheet workable. I'm not to sure if I understood his needs, but I'm interested to know what your code does, and how it solves Dane's problem.
    PS --- Please review my code in the attachment and comment on it too. As I played around with the request (as I understood it) and come up with this!!!
    Thanx 4 your time
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Disable all controls on a worksheet temporarily? (2000/sp3)

    Your code explicitly lists the controls to be disabled; you can use the syntax Me.CheckBox1 for controls created from the Control Toolbox, but not for controls created from the Forms toolbar.

    All controls, whether from the Control Toolbox or from the Forms toolbar, are part of the Shapes collection of a worksheet. Any drawing objects created from the Drawing toolbar also belong to this collection. The code I posted loops through all shapes on a worksheet, and disables or enables them. This is handy if you have a lot of controls, and don't want to list them individually.

    In some situations your code is to be preferred, since you can decide which controls will be enabled/disabled. In other situations, my 'brute force' method is easier.

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Disable all controls on a worksheet temporarily? (2000/sp3)

    <hr>All controls, whether from the Control Toolbox or from the Forms toolbar, are part of the Shapes collection of a worksheet. Any drawing objects created from the Drawing toolbar also belong to this collection. <hr>

    I (in my wildest dreams), would not have thought that worksheet controls could belong to the Shapes collection!! Sorry to persist on this point, but when would you refer to the Controls collection then? (I'm ignorant here!)

    Secondly: I see you assigned an argument to your code, (Flag as Boolean). How would Dane run this macro as there is no prompt to collect a value!
    Could you have a macro something like this:
    <pre>Sub ToggleControls()
    Dim ControlState as Boolean
    ControlState = SetControls
    SetControls = Not ControlState
    End Sub
    </pre>

    I don't think the code is correct...but something like it!

    Thanx 4 your patience!
    Regards,
    Rudi

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Disable all controls on a worksheet temporarily? (2000/sp3)

    A worksheet does not have a Controls collection. There is no built-in collection that is specific to controls, so you need to use the Shapes collection, which may contain other elements too.

    SetControls as I posted it is not a function, it does not return a value. It is not a macro either, since it expects an argument Flag. It is a procedure that can be called by other code. You could define a macro to disable controls:

    Sub DisableControls()
    SetControls False
    End Sub

    You can also adapt your idea of using a boolean variable, but it should be a module-level or global variable:

    ' Declare variable outside procedure
    Dim ControlState As Boolean

    Sub ToggleControls()
    ControlState = Not ControlState
    SetControls ControlState
    End Sub

    Note: a userform created in the Visual Basic Editor does have a Controls collection.

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Disable all controls on a worksheet temporarily? (2000/sp3)

    Many thanx Hans. You have absolutely cleared up all my queries about this!
    Thankyou for your time and expertise!
    I will not hassle you again....TODAY (that is) <img src=/S/joy.gif border=0 alt=joy width=23 height=23> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  11. #11
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable all controls on a worksheet temporarily? (2000/sp3)

    Rudi,

    Your mechanism is very nice! I like the idea of creating my OWN collection, and include those items I see fit. I may incorporate your mechanism in future projects. Thank you for that attachment.

    I wanted to also mention that part of the reason I enjoyed HansV's code is because it is so simple. I basically want to completely disable the worksheet while I go process various files. However, since this "interface" is still under development, and may in the future be maintained by someone other than myself with less experience in Excel, I did not want to run the risk of controls added in the future NOT being disabled while the scripts execute...

    My only followup question is whether or not you can test for the "type" of a control? For instance, if a control is just a label, I may not want to disable it (just for visual aspects of the interface, nothing functional here). Not terribly important for what I'm doing, though. Just makes for a more polished interface...

    thanks to all!
    ..dane

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Disable all controls on a worksheet temporarily? (2000/sp3)

    Each Shape object has a Type property. This is one of the msoShapeType constants. Controls from the Forms toolbar have Type = msoFormControl = 8, and controls from the Control Toolbox have type msoOLEControlObject = 12.

  13. #13
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Disable all controls on a worksheet temporarily? (2000/sp3)

    Hi Dane,

    I pulled this example from the VBA helpfiles. The example below shows the controls types from the Forms toolbar. But it proves that you can single out a control type and work with it!

    Example
    This example clears all the Microsoft Excel check boxes on worksheet one.
    For Each s In Worksheets(1).Shapes
    If s.Type = msoFormControl Then
    If s.FormControlType = xlCheckBox Then
    s.ControlFormat.Value = False
    End If
    Next


    Returns the Microsoft Excel control type. Read-only XlFormControl.
    XlFormControl can be one of these XlFormControl constants.
    xlButtonControl
    xlCheckBox
    xlDropDown
    xlEditBox
    xlGroupBox
    xlLabel
    xlListBox
    xlOptionButton
    xlScrollBar
    xlSpinner
    Regards,
    Rudi

  14. #14
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable all controls on a worksheet temporarily? (2000/sp3)

    Thanks for that information! Unfortunately, my 'shapes' are all of type msoOLEControlObject rather than type msoFormControl, so your example doesn't work in my situation... Surely there are similar defines for msoOLEControlObject objects?

    thanks!
    ..dane

  15. #15
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable all controls on a worksheet temporarily? (2000/sp3)

    Hans,

    How does this help me? I know (now) that my objects are all of type "msoOLEControlObject", but that doesn't help me determine what kind of object they are (listbox, label, etc). I can't seem to find help on the topic, either.. Am I just overlooking it?

    ..dane

Page 1 of 2 12 LastLast

Posting Permissions

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