+ Reply to Thread
Results 1 to 7 of 7

code to work for more number of rows.

  1. #1
    Registered User
    Join Date
    08-25-2016
    Location
    uk
    MS-Off Ver
    2010
    Posts
    40

    code to work for more number of rows.

    Hi experts,

    I have the following code below and I would like this code to run from (A1 - A1000) affecting (B1 - B1000).
    Please help?


    Private Sub Worksheet_Activate()
    If [A1].Value = "Other" And [B1].Value = "" Then
    [B1].Value = InputBox("What is the reason", "Other")
    Worksheet_Activate
    End If
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheet_Activate
    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Worksheet_Activate
    End Sub



    any help would be really appreciated.

    Thanks
    Exceltriumph

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: code to work for more number of rows.

    How are these cells being changed?

  3. #3
    Registered User
    Join Date
    08-25-2016
    Location
    uk
    MS-Off Ver
    2010
    Posts
    40

    Re: code to work for more number of rows.

    Hi Dave,

    So here is what i am working on:

    I have a dropdown list with 5 options in it.
    1. reject
    2. accept
    3. scrap
    4. missing
    5. other

    When the user selects option 5 (other), I want excel to force the user to enter the (other reason) in the next column.

    NOTE: this is only for option 5 and not for any other options.

    The drop down list is in column (A1 - A1000) and the respective note should be added in (B1 - B1000), only when "other" is selected.

    For this the code I use is:

    Private Sub Worksheet_Activate()
    If [A1].Value = "Other" And [B1].Value = "" Then
    [B1].Value = InputBox("What is the reason", "Other")
    Worksheet_Activate
    End If
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheet_Activate
    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Worksheet_Activate
    End Sub

    The code works fine, but only for A1 and B1, I would like it to work for (A1 - A1000) affecting (B1 - B1000).
    Please help?

    hope that clarifies my question. I really appreciate your help.

    Thankyou

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: code to work for more number of rows.

    You can replace all the code with this.

    Please Login or Register  to view this content.
    Find my examples on worksheet events
    http://www.xlorate.com/vba-examples....sheet%20Events

  5. #5
    Registered User
    Join Date
    08-25-2016
    Location
    uk
    MS-Off Ver
    2010
    Posts
    40

    Re: code to work for more number of rows.

    Hi Dave,

    Thanks for the code,

    It works great, but it hasn't solved my problem yet.

    I only want excel to ask me for the a reason if other was selected on the drop down list,

    when any of the other values are selected (i.e. reject, accept, scrap, missing) the dialogue box does not have to pop-up.

    Many thanks for your guidance.

    PS: the Xlorate links and examples are great, i will practice them soon.

    Cheers.

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: code to work for more number of rows.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-25-2016
    Location
    uk
    MS-Off Ver
    2010
    Posts
    40

    Re: code to work for more number of rows.

    Thank you very much Dave,

    worked liked magic :D

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How can i get this code to work across several rows
    By deek in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-18-2016, 01:20 PM
  2. [SOLVED] Help Adjusting VBA Code Work On New Rows
    By avendi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-30-2015, 07:21 PM
  3. [SOLVED] Help to code not work on Sheet with many rows and columns.
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-14-2015, 10:26 AM
  4. Help to code not work on Sheet with many rows and columns.
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-14-2015, 09:51 AM
  5. Update Code to work with multiple rows
    By atomichybrid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-13-2014, 06:07 PM
  6. [SOLVED] code to work for odd rows only
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-04-2014, 10:58 AM
  7. [SOLVED] Code to work on rows 9-220 only
    By Grf in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-12-2012, 05:49 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1