6

I need some help Regarding VBA. In My code I add an excel sheet and rename it and add a validation list to it.I need to run some code on changing value in that validation list.And that must run only on change of that particular cell. If I am not clear please let me know.Please Help me solving this.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
krishna
  • 3,148
  • 5
  • 19
  • 24
  • 2
    Start here if you want to add code dynamically to a sheet: http://www.cpearson.com/excel/vbe.aspx – Tim Williams Feb 26 '13 at 06:57
  • hi @Tim Williams , For this I need to add Code to particular Sheet. Here I am facing another Problem. I can add code by referencing Sheet Index. And as I am renaming the sheet , and sheet Index and position are always not same, how could I do that. Is there any way of getting Sheet Index. Some function etc.,??? – krishna Feb 26 '13 at 08:23
  • Can you specify your question further? After adding and renaming a (copied?) sheet, what exactly do you need to add to this sheet? Why? – Peter Albert Feb 26 '13 at 08:57
  • Am creating a "NEW" Sheet by calling a macro on Button CLick.So while creation of sheet itself i need to add some Code for "Worksheet_change" event – krishna Feb 26 '13 at 11:10
  • but is the worksheet_change event pretty much the same for each new sheet? (ps: please reply with @peteralbert, else I will not be notified) – Peter Albert Feb 26 '13 at 11:31
  • @peterAlbert Thanks for reply.. I need same functionality for all sheets added by this Button – krishna Feb 26 '13 at 12:18
  • then instead of using a the `Worksheet_Change` event, simply use the `Workbook_SheetChange` event - this you only need to place once in ?ThisWorkbook`and it'll handle all worksheets. Just a condition in the beginning of the event, checking if any of the affected worksheet is changed... – Peter Albert Feb 26 '13 at 12:21
  • @peteralbert Ok.. But there are few work sheets for which it should not apply.. But I can Use It. I would be more great full if ou explain me about CodeName variable... – krishna Feb 26 '13 at 12:24
  • @peteralbert once i get code variable for new sheet my task is done. But am getting CodeName only after completion of Button Event – krishna Feb 26 '13 at 12:29
  • sorry, don't know when `.CodeName` is set. However, in most cases, you can simply address it with `Worksheets("normal name")` or `Sheets(Sheets.Count)` for a newly inserted sheet – Peter Albert Feb 26 '13 at 12:37
  • You're welcome! I summarized the comments as an answer for future readers. If it helped, please accept it as an answer. – Peter Albert Feb 26 '13 at 12:49

2 Answers2

7

Instead of trying to create the individual code for each new worksheet with the Visual Basic Extensibility (see this link for further reading), simply use the Workbook wide event Workbook_SheetChange (you need to place it in the ThisWorkbookmodule).

In this event code first check, if the worksheet which caused the event is one of the newly created worksheets. This can be done most easily, be checking the .Name of the worksheet.

Community
  • 1
  • 1
Peter Albert
  • 16,917
  • 5
  • 64
  • 88
0

you can use SelectionChange Event and Change event or it is also possible to use the event Thisworkbook module. SheetChange or SheetSelectionChange.

Peter Albert
  • 16,917
  • 5
  • 64
  • 88
didierdrogod
  • 1
  • 2