Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > BOOK: Excel VBA 24-Hour Trainer 2nd edition
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Excel VBA 24-Hour Trainer 2nd edition
This is the forum to discuss the Wrox book Excel VBA 24-Hour Trainer 2nd Edition by Tom Urtis; ISBN: 978-1-118-99137-4
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel VBA 24-Hour Trainer 2nd edition section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old April 15th, 2016, 07:02 AM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2016
Location: Lahore, Pakistan
Posts: 5
Thanks: 3
Thanked 0 Times in 0 Posts
Send a message via Skype™ to umair0202
Post How to Set Variable Offset command in VBA????

I have a sheet for monthly subscription fee record. Column "A" lists Serial Number, Column "B" shows Description of Subscription, Column "C" to "N" list months.
I want to create a code so whenever I enter the submitted fee amount in any month (Column "C" to Column "N") the current date is automatically updated in Column "P" for that subscription.

Using Offset command following command does the work but only for one column. If I make an entry to next column the date is entered in Column "Q" onward.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range, n As Range, Inte As Range, r As Range
    Set A = Range("C:C")
    Set Inte = Intersect(A, Target)
    If Inte Is Nothing Then Exit Sub
    Application.EnableEvents = False
        For Each r In Inte
            r.Offset(0, 13).Value = Date
            
        Next r
    Application.EnableEvents = True
End Sub
Please advise how I can design a formula which allows me to set target a fixed target column for a range of columns (C:C:N:N) in the same row as selected for an entry.
Reply With Quote
  #2 (permalink)  
Old April 15th, 2016, 04:37 PM
Wrox Author
Points: 213, Level: 4
Points: 213, Level: 4 Points: 213, Level: 4 Points: 213, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2015
Location: San Francisco, California, USA
Posts: 34
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Try replacing the Change event that you posted (which does not work the way you want) with this instead:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column < 3 Or .Column > 14 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Cells(.Row, 16).Value = Date
End With
End Sub
The two issues that might come into play are your preference of formatting the date in column P, and if the contents of a cell among columns C:N get deleted, should you want to change the date in column P in that case. Up to you, but the above code will essentially do what you are asking for.
Reply With Quote
  #3 (permalink)  
Old April 16th, 2016, 09:34 AM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2016
Location: Lahore, Pakistan
Posts: 5
Thanks: 3
Thanked 0 Times in 0 Posts
Send a message via Skype™ to umair0202
Default

Quote:
Originally Posted by Tom Urtis View Post
Try replacing the Change event that you posted (which does not work the way you want) with this instead:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column < 3 Or .Column > 14 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Cells(.Row, 16).Value = Date
End With
End Sub
The two issues that might come into play are your preference of formatting the date in column P, and if the contents of a cell among columns C:N get deleted, should you want to change the date in column P in that case. Up to you, but the above code will essentially do what you are asking for.
Thank you Tom for your help.

Is this code to be replaced completely with the code I posted or it is to be replaced within the original code to get the required results ?

Looking forward your valued guidance.

Warm Regards,
Reply With Quote
  #4 (permalink)  
Old April 16th, 2016, 10:48 AM
Wrox Author
Points: 213, Level: 4
Points: 213, Level: 4 Points: 213, Level: 4 Points: 213, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2015
Location: San Francisco, California, USA
Posts: 34
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Delete your entire original Change event code -- all the code you first posted and showed on this thread, every line of it, including the line
Private Sub Worksheet_Change(ByVal Target As Range)
and the line
End Sub
and everything in between,
then copy the full complete code I posted, exactly as you see that I posted it, and paste my copied full code into your worksheet module.

There cannot be more than one procedure name (in this case the Change event procedure) in the same worksheet module, which is why you need to totally replace all the code you first posted with all the code I posted.
Reply With Quote
The Following User Says Thank You to Tom Urtis For This Useful Post:
umair0202 (April 17th, 2016)
  #5 (permalink)  
Old April 17th, 2016, 06:55 AM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2016
Location: Lahore, Pakistan
Posts: 5
Thanks: 3
Thanked 0 Times in 0 Posts
Send a message via Skype™ to umair0202
Default

Thank you so much Tom, the code is working perfectly.

I have just started learning VBA. I will study in detail the With Target command as you used in given code for understanding and will again request your kind guidance if required for any clarification of understanding.

Thanks again for your valued help.
Reply With Quote
  #6 (permalink)  
Old April 18th, 2016, 05:17 AM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2016
Location: Lahore, Pakistan
Posts: 5
Thanks: 3
Thanked 0 Times in 0 Posts
Send a message via Skype™ to umair0202
Default

Quote:
Originally Posted by Tom Urtis View Post
Delete your entire original Change event code -- all the code you first posted and showed on this thread, every line of it, including the line
Private Sub Worksheet_Change(ByVal Target As Range)
and the line
End Sub
and everything in between,
then copy the full complete code I posted, exactly as you see that I posted it, and paste my copied full code into your worksheet module.

There cannot be more than one procedure name (in this case the Change event procedure) in the same worksheet module, which is why you need to totally replace all the code you first posted with all the code I posted.
Dear Tom,

I have tried to understand the code you provided.
bellow I am pasting the code with my comments that what I understand and what not.
Please guide me about the function of the code line I am unable to understand.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column < 5 Or .Column > 10 Then Exit Sub
' This code line sets that the code will only work if the column number is from 5 to 10

If Target.Cells.Count > 1 Then Exit Sub
' what is the function of this code line ?

Cells(.Row, 13).Value = Date
' this code line defines that in current row cell in column 13 date value is to be entered

End With
End Sub
Thanks a lot for your worthy guidance.
Reply With Quote
  #7 (permalink)  
Old April 18th, 2016, 11:16 AM
Wrox Author
Points: 213, Level: 4
Points: 213, Level: 4 Points: 213, Level: 4 Points: 213, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2015
Location: San Francisco, California, USA
Posts: 34
Thanks: 0
Thanked 5 Times in 5 Posts
Default

You wrote 3 comments in your code, taking each in turn:

Your comment #1:
"'This code line sets that the code will only work if the column number is from 5 to 10"
Correct, you understand that the range of columns is from column 5 (commonly known as column E because E is the 5th letter of the alphabet) to column 10 (which is column J, the 10th letter of the alphabet).

Your comment #2:
If Target.Cells.Count > 1 Then Exit Sub
"what is the function of this code line ?"
That code line says that if you try to change more than 1 target cell (that is, in this case, a cell between and including among columns E:J) that no date will be produced in column 13. The code assumes you only want to enter or edit one cell at a time. I suppose you could edit that line to say
If Target.Cells.Count > 6 Then Exit Sub
which would mean that if you copy and paste data along one row in all 6 target cells at once (which would change the cells in columns E, F, G, H, I, and J) then that would allow for the current date to show up in column M. Most users prefer to guard against their event code(s) being triggered when more than one cell at a time changes. Such a safeguard helps control data entry by their users. Also, there are problems that might occur if an event is triggered when not in a "one change, one trigger" mode. But if you as the workbook developer deem it OK for many cells to be changed simultaneously for an event to be triggered, which comes with experience and a fuller understanding of the ramifications be they positive or negative, then it is possible as I suggested. I would exercise caution to make sure you know all the ramifications of Change events (and other events that may be present) when triggered by more than one change at the same time.

Your comment #3:
"'this code line defines that in current row cell in column 13 date value is to be entered"
Correct, you understand that the Cells range reference property is such that the actual worksheet cell being referred to is identified by its row number (in this case, the row of the Target cell being changed) and column number 13, which is column M. Therefore, based on that code line, the current date will appear in the cell in column M of the row where a cell was just changed among and including columns E:J.
Reply With Quote
The Following User Says Thank You to Tom Urtis For This Useful Post:
umair0202 (April 19th, 2016)
  #8 (permalink)  
Old April 19th, 2016, 10:36 AM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2016
Location: Lahore, Pakistan
Posts: 5
Thanks: 3
Thanked 0 Times in 0 Posts
Send a message via Skype™ to umair0202
Default

Thank you very much for your time and guidance.


I tried the discussed code line with 3 instead of 1. Code is below.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column < 3 Or .Column > 14 Then Exit Sub
If Target.Cells.Count > 3 Then Exit Sub
Cells(.Row, 16).Value = Time
' Time value set so the difference can be observed for repeat entry in Target Cell
End With
End Sub
There is no obvious difference.

I am confused about this.

Your help is requested.

Last edited by umair0202; April 19th, 2016 at 10:46 AM.
Reply With Quote
  #9 (permalink)  
Old April 19th, 2016, 12:57 PM
Wrox Author
Points: 213, Level: 4
Points: 213, Level: 4 Points: 213, Level: 4 Points: 213, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2015
Location: San Francisco, California, USA
Posts: 34
Thanks: 0
Thanked 5 Times in 5 Posts
Default

It will look as if there is no difference in the result, because the second If statement is totally unnecessary and irrelevant, and in fact it essentially is not even executed. The first If statement trumps the second statement because it speaks to the condition of the triggerable range of columns. Once a cell is changed in a column less than C (that is, in either column A or B), OR a cell in a column past column N, the event will not trigger and the Sub will be exited then and there, just as that line of code says:
If .Column < 3 Or .Column > 14 Then Exit Sub
There is not only no reason, but illogical and useless to put that second If statement in that code as you did. The first If statement fully handles the decision-making about which columns are included and which are excluded.

By the way, the premise of your logic is inconsistent between the two If statements.
If .Column < 3 Or .Column > 14 Then Exit Sub
includes column 3 (column C).
If Target.Cells.Count > 3 Then Exit Sub
excludes column C.
But again, that second If statement is irrelevant anyway.
Reply With Quote
The Following User Says Thank You to Tom Urtis For This Useful Post:
umair0202 (April 20th, 2016)
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
command text was not set for the command objec needy Classic ASP Databases 1 April 8th, 2009 04:27 PM
Object variable not set or With Block not set brucechess BOOK: Beginning VB.NET Databases 1 March 3rd, 2009 07:30 AM
Command text was not set for the command object Sheraz Khan Classic ASP Databases 2 May 29th, 2007 01:57 AM
Command text was not set for the command object. deepa12 BOOK: Beginning ASP 3.0 5 November 2nd, 2004 05:37 PM



All times are GMT -4. The time now is 08:16 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.