Results 1 to 6 of 6

Monitor multiple cells for an input event

Threaded View

iwannabakat Monitor multiple cells for an... 05-07-2014, 08:08 PM
JBeaucaire Re: Monitor multiple cells... 05-07-2014, 09:26 PM
iwannabakat Re: Monitor multiple cells... 05-07-2014, 10:07 PM
JBeaucaire Re: Monitor multiple cells... 05-07-2014, 10:31 PM
iwannabakat Re: Monitor multiple cells... 05-07-2014, 10:45 PM
JBeaucaire Re: Monitor multiple cells... 05-08-2014, 02:53 AM
  1. #1
    Registered User
    Join Date
    05-07-2014
    Location
    Prescott, AZ
    MS-Off Ver
    Excel 2007
    Posts
    35

    Monitor multiple cells for an input event

    Below is code supplied by JBeaucaire and it works exactly as I requested. However, I need this to happen for multiple cells.

    The cells that I need to monitor are B10, B12, B14.... through B96 and F, J, N, R columns for the same rows. The input values will be stored in cells AM10, AM12, AM14 .... through 96 for the B column results. Then for F, J, N, R will be AQ, AU, AY and BC respectively.

    Additionally, when B10 has an input, and the user input box displays for the number of hours, there is a cancel button available, but it does not work. How can I exit the loop and clear contents of B10 if the "Cancel" button is clicked in the dialogue box.

    I know I'm asking alot. thanks in advance.


    Option Explicit
    
    Private Sub Worksheet_Activate()
        If Range("B10") = "" And Range("AM10") <> "" Then Range("AM10").ClearContents
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyVal As Single
    
        If Not Intersect(Target, Range("B10")) Is Nothing Then
            If Range("B10") <> "" Then
                Do
                    MyVal = Application.InputBox("How many hours for this task: " & Range("B10").Value, "Hours", Type:=1)
                    If MyVal > 0 Then
                        Range("AM10").Value = MyVal
                        Exit Do
                    End If
                Loop
            Else
                Range("AM10").ClearContents
            End If
        End If
    
    End Sub
    Last edited by JBeaucaire; 05-07-2014 at 09:15 PM. Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sheet Change Event Dubuggin: Restrict Cell Values toand Changing cells based on input
    By cmore in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 10-28-2013, 02:37 PM
  2. Returning multiple values in multiple cells based upon one input
    By ccoonsk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-14-2012, 10:01 AM
  3. Monitor Combobox Input
    By wildjester in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-13-2009, 03:27 AM
  4. [SOLVED] change event for multiple cells
    By Guy Normandeau in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-20-2006, 04:20 PM
  5. How to display multiple pages on one large monitor
    By mrkrug3 in forum Excel General
    Replies: 1
    Last Post: 06-23-2005, 09:05 PM

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