Excelvba

Question

Hi There

I am trying to add a warning message to a Macro I have created. I would like to space out the question over a couple of lines so it is easy to read. Every time I type have the message and press enter to add the other half of the message I get an error message. The code I am using is:

Dim warning warning = MsgBox(Range("A1").Value & " warning", vbOKCancel, "Warning") If warning = vbCancel Then Exit Sub

Do you know how I can add the warning message over 3 lines?

Answer

If you enter new lines within the text in cell A1 by hitting alt+enter where you want a line break to appear, then the message box will show your message on different lines.

If you have fixed text, do it like this:

MsgBox "Line 1" & vbNewLine & "Line 2" & vbNewLine

Question

hope you can, and thank in advance!

I have a workbook with 7 worksheets. These worksheets are building off of each each to ultimately produce information in a summary sheet at the end. I am using code to initiate change events to ensure that the current sheet is updating the summary sheet.

I am at a point where I have coded a copy worksheet event in Module 1 of the project, but when I run the copy event, it copies over the worksheet and the vbcode in that worksheet, but I don't want it to.

Is there a way to delete the associated code to that worksheet when you use the copy event, or can you provide some generic code to delete the code when the worksheet is copied?

I guess ultimately I want create a new module in that worksheet when it is copied, but for some reason I am unable to delete the old code first.

Any options, information or help you could provide would be greatly appreciated.

Wade

Answer

Example: you want to use vba code in a module to delete all of the vba code attached to the Sheet1 object of the same workbook.

First, in order for vba code to alter other vba code, do this: From the Developer ribbon, choose Macro Security, Macro Settings, Developer Macro Settings, and check "Trust access to the VBA project object model"

Then, in order to use vba objects in your code, do this: From within the Visual Basic Editor, select from menu Tools, References. Click to select the Microsoft Visual Basic for Applications Extensibility check box and then click OK.

Now you're ready to use code like this, to delete all the vba code attached to Sheet1 of the workbook:

Sub DeleteAllCodeFromSheet1()

   Dim codeMod As VBComponent
   Set codeMod = ActiveWorkbook.VBProject.VBComponents("Sheet1")
   codeMod.CodeModule.DeleteLines 1, codeMod.CodeModule.CountOfLines

End Sub