Consulting

Results 1 to 8 of 8

Thread: Locking Form Controls

  1. #1
    VBAX Contributor Glaswegian's Avatar
    Joined
    Sep 2004
    Location
    Glasgow, Scotland
    Posts
    196
    Location

    Locking Form Controls

    Hi again

    I have a multi page userform with a variety of controls. Briefly, users complete certain fields, option buttons etc and data is transferred to a hidden sheet. Users can recall this data but cannot make any further amendments. To ensure they cannot amend any data I'm using this code, which works fine:
    For Each ctl In frmMain.Controls
        If Not TypeOf ctl Is MSForms.MultiPage Then
            If Not ctl.Name Like "*Close*" Then
                ctl.Object.Enabled = False
            End If
        End If
    Next ctl
    This ensures that several 'Close' command buttons are available to close the form.

    However, disabling the controls makes the data slightly difficult to read. Is there a way to loop through and Lock the controls, rather than disable them? Would I need to loop through each control type - combobox, textbox optionbutton etc?

    Thanks for any assistance or suggestions.
    Iain - XL2010 on Windows 7

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Why does disbaling them make it difficult to read?

    What do YOU men by lock?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    Would it work to set the control's .Visible property?

  4. #4
    VBAX Contributor Glaswegian's Avatar
    Joined
    Sep 2004
    Location
    Glasgow, Scotland
    Posts
    196
    Location
    Apologies if I was not clear.

    When the controls are disabled Excel makes the font fainter than it was. By locking, I mean that the control will look as normal but the user will not be able to amend the value, for example, when the form is loaded, I format the date field and then lock it, so that users cannot change it
     .txbDate.Value = Format(Date, "dd mmmm yyyy")
    .txbDate.Locked = True
    The controls need to be visible.
    Iain - XL2010 on Windows 7

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You could use a Boolean variable that is module scope, and set that to indicate disabled, and then check it in the controls event. Something like this air code

    [vba]

    Private fDisabled As Boolean

    Private Sub cmClose1_Click()

    If Not fDisabled Then

    'do your stuff
    End If
    End SUb
    [/vba]

    You will have to determine where the Boolean gets set to True and reset back.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Contributor Glaswegian's Avatar
    Joined
    Sep 2004
    Location
    Glasgow, Scotland
    Posts
    196
    Location
    I understand what you mean, but I don't want the fields disabled, I would prefer them to be locked. This means they would still retain the 'look' of an unlocked field, but users would not be able to amend any data.
    Iain - XL2010 on Windows 7

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    They wouldn't be disabled, you just cripple the event action.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Contributor Glaswegian's Avatar
    Joined
    Sep 2004
    Location
    Glasgow, Scotland
    Posts
    196
    Location
    Doh! Thanks!
    Iain - XL2010 on Windows 7

Posting Permissions

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