Using the WITH Statement with more than one object?

[RyanH]

I am a novice to VBA and this is probably an easy fix, but I don't know. Is
it possible to use the WITH Statement with more than one Object? For example,
I have a Check Box that I use to Enable two Text Boxes in a UserForm. Here
is my code:
Private Sub chkChLtrs_Click()

With txbChLtrsDate
If chkChLtrs.Value = True Then
.Enabled = False
.Locked = True
Else
.Enabled = True
.Locked = False
End If
End With

With txbChLtrsHrs
If chkChLtrs.Value = True Then
.Enabled = False
.Locked = True
Else
.Enabled = True
.Locked = False
End If
End With
End Sub

Can this be shortened in anyway? For Example:

With txbChLtrsDate AND txbChLtrsHrs
' common code
End With

 

[Tony Jollans]

You can only have one active With statement - and one active With Object -
at any one time.

In your particular example it could be written in different ways but there
isn't much you can do to shorten it. If it were a challenge to write the
shortest code, yes, it could be shortened but it would then become much
harder to maintain and there wouldn't be any significant run time gain.

 

[RyanH]

Thanks for the quick response and the input!

 

[NZ VBA Developer]

Ryan,

You might also want to look at toggling the .TabStop and .BackColor as well;
just setting .Enabled to False doesn't stop the TextBox receiving focus or
change the appearance. &H8000000F and &H80000005 should give you the look you
want. Clearing the Value from a disabled TextBox might also be a
consideration as well.

 

[fumei via OfficeKB.com]

I agree with Tony. It certainly could be written differently, but with
doubtful gain in efficiency. I would however like to point out that there
are two separate but equal IF statements.

You make TWO separate instructions checking IF chkChLtrs.Value = True. It
may be better to use that as the basis for your logic, and make ONE
instruction (although multiple actions).

Select Case chkChLtrs.Value
Case True
With txbChLtrsDate
.Enabled = False
.Locked = True
End With
With txbChLtrsHrs
.Enabled = False
.Locked = True
End With
Case False
With txbChLtrsDate
.Enabled = True
.Locked = False
End With
With txbChLtrsHrs
.Enabled = True
.Locked = False
End With
End Select

 

[RyanH]

Thanks for the input!

Ryan,

You might also want to look at toggling the .TabStop and .BackColor as well;
just setting .Enabled to False doesn't stop the TextBox receiving focus or
change the appearance. &H8000000F and &H80000005 should give you the look you
want. Clearing the Value from a disabled TextBox might also be a
consideration as well.
--
Cheers!

The Kiwi Koder
Go the All Blacks!

 

[RyanH]

Thanks for the input! I actually just came across the Select Case Structure
in my book and I was trying to make it work near the end of the day. Thanks
Again!