+ Reply to Thread
Results 1 to 9 of 9

Message Window to Execute Code

  1. #1
    Registered User
    Join Date
    12-03-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    63

    Message Window to Execute Code

    Dear Friends,

    I have a VBA that copies the formula in Range "A6:C6" to range "A6:C5000". This code is activated when the user press a button displayed on the sheet.

    But what I want is that when user insert or delete row/rows, they should get a message window " You have inserted or deleted row/rows, please click 'OK' to update the formula in Sheet" (Option to click 'OK') and then the code should be executed. Given below is the code.

    Please Login or Register  to view this content.
    Kindly let me know how to have a message window displayed when user insert/delete row/rows and clicking ok will execute the code.

    Thanks,
    Rahul

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Message Window to Execute Code

    Why not just use a Worksheet Change Event handler?
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    12-03-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: Message Window to Execute Code

    Hi TMS,
    Thanks for your quick reply and suggestion.

    I don't know as how the worksheet change event handler works. If you can give me some more information, that will be helpful. How to activate worksheet event handler?

  4. #4
    Registered User
    Join Date
    12-03-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: Message Window to Execute Code

    Hi TMS, I looked into worksheet change event handler. I don't think it will be much helpful to me. In my case the user can select many rows and right click and insert or delete row. They can select any row/rows in range A6:C5000.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Message Window to Execute Code

    But I suggest you experiment with it. There is no event, AFAIK, for inserting or deleting a row but I would expect it to fire the Change Event handler as other cells would change. Might be wrong and I’m not in a position to test it right now.

    You could also investigate using the Calculate Event handler. That, however, might slow things down.

  6. #6
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Message Window to Execute Code

    In your worksheet module
    Please Login or Register  to view this content.
    In a standard module
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-03-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: Message Window to Execute Code

    Hi Nigelog,

    Thanks for your reply. While adding the code in worksheet module, I am getting error "Compile error : Ambiguous name detected : Worksheet_SelectionChange". I think this is related to another worksheet_SelectionChange written in the worksheet module. The whole code in the worksheet module is given below. The last part of the code is the one you suggested. Kindly let me know who to get rid of this error.

    Please Login or Register  to view this content.

  8. #8
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Message Window to Execute Code

    Try
    add
    Please Login or Register  to view this content.
    delete
    delete
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    12-03-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: Message Window to Execute Code

    Thanks again Nigelog.. I made the change in the code as suggested by you. But now I keep on getting the message i.e "You have added a row number__" . Even if I click 'OK' the message appear again and again . I cannot close the excel, and I have to use Task Manager to close the excel. Given below is the code which I corrected after your suggestion.

    Please Login or Register  to view this content.
    Last edited by Rahul15292000; 05-29-2019 at 04:51 PM.

+ 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] Can't Execute Message Box
    By bralew in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-10-2017, 09:30 AM
  2. [SOLVED] Yes/No message box - If no execute code
    By michelle 1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-22-2016, 09:33 AM
  3. code to execute a keyboard shortcut or to execute a toolbar button
    By Gti182 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-09-2015, 12:16 PM
  4. [SOLVED] Open one command window and execute a series of commands from it.
    By Air_Cooled_Nut in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-03-2014, 11:48 AM
  5. VBA code to open and execute files from different directories in Command window
    By AlexXYZ in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-27-2012, 01:49 PM
  6. VBA code to open and execute files from different directories in Commend window
    By AlexXYZ in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-27-2012, 01:52 AM
  7. Can vba code identify duplicates numbers in a column via a message window?
    By lilsnoop in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2012, 07:51 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