Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / September 2007

Tip: Looking for answers? Try searching our database.

Using the WITH Statement with more than one object?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
RyanH - 27 Sep 2007 16:28 GMT
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 - 27 Sep 2007 16:42 GMT
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.

Signature

Enjoy,
Tony

>I am a novice to VBA and this is probably an easy fix, but I don't know.
>Is
[quoted text clipped - 31 lines]
>    ' common code
> End With
RyanH - 27 Sep 2007 17:34 GMT
Thanks for the quick response and the input!

> 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,
[quoted text clipped - 28 lines]
>     ' common code
> End With
NZ VBA Developer - 28 Sep 2007 00:22 GMT
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.
Signature

Cheers!

The Kiwi Koder
Go the All Blacks!

> 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,
[quoted text clipped - 28 lines]
>     ' common code
> End With
RyanH - 28 Sep 2007 13:16 GMT
Thanks for the input!

> Ryan,
>
[quoted text clipped - 36 lines]
> >     ' common code
> > End With
fumei - 28 Sep 2007 01:53 GMT
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 - 28 Sep 2007 13:20 GMT
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!

> 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
[quoted text clipped - 24 lines]
>             End With
> End Select
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.