Inserting code into Sheet1 using macro

[channel7]

I have a bit of code which performs correctly but it ONLY works if it is pasted into the worksheet using VBA. I currently have to manually copy/paste it before it will work and I am needing a macro to handle inserting this for me.

Any ideas on how to get a macro to copy/paste into Sheet1 under the VBAProjects heading?

Thanks!!!

 

[channel7]

I may not be using the correct terminology to describe the result I am desiring. If you see anything that needs clarification please just ask.

Thanks

 

[erik.van.geit]

Hi,

This sounds too vague to my sense.
Why would it

ONLY work if it is pasted into the worksheet using VBA

You are probably not meaning that it will only work if VBA is used to paste it in the worksheet module, are you?
I feel that you are suggesting a solution instead of putting the problem itself? So can you tell exactly what you are willing to do?

kind regards,
Erik

 

[channel7]

Sorry I sounded close-minded in my asking for help. I am willing to use any advice and not suggesting a solution but trying to describe the task/problem.

To try and make things clearer, this is the code I use:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
If ActiveCell.Row > 16 Then
Dim myShape As Shape
Set myShape = Me.Shapes("Button 1")
With Me.Cells(1, ActiveWindow.ScrollColumn)
myShape.Top = 30 '.Top
myShape.Left = .Left
End With
End If
End Sub

It works great at keeping my button visible on the worksheet after it has been autofiltered. The problem is the code won't work if it is in a module. It has to be hard coded into the sheet itself. I have to open the visual basic editor and insert the code under VBAProject-Microsoft Excel Objects-Sheet1.

My question then is, how can I get a macro to insert the code at the Sheet level?

Thanks

 

[erik.van.geit]

To let VBA write code is not that difficult, but it is still not clear why you would like to do this. Writing code within a macro should rather be an exception, so I would advise to avoid it.

So, to my sense you are still one step too far. What do you want to do in the first place?
Are you creating a sheet using VBA?

see this thread
http://www.mrexcel.com/forum/showthread.php?t=295316
but again, I'm not quite sure that you would need it

 

[channel7]

The code you posted in that link does exactly what I was trying to describe. Thanks!

Now I need help modifying it to work for my application. I have the code placing the desired code exactly where I want it but it will not compile once it is pasted in. For some weird reason I cannot include the quotes around the button's name in the macro performing the work but the code will not compile if the quotes are not around the button's name. It errors out saying Expected: Expression


Example of the code in the macro doing the work to enter the code into Sheet1:

'add sheet and code
Set sht = Workbooks("Workbook.xls").Worksheets("Sheet1")
shtCode = _
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)" & vbNewLine & _
"Application.DisplayCommentIndicator = xlCommentIndicatorOnly" & vbNewLine & _
"If ActiveCell.Row > 16 Then" & vbNewLine & _
"Dim myShape As Shape" & vbNewLine & _
"Set myShape = Me.Shapes(Select Row then Click here)" & vbNewLine & _
"With Me.Cells(1, ActiveWindow.ScrollColumn)" & vbNewLine & _
"myShape.Top = 30 '.Top" & vbNewLine & _
"myShape.Left = .Left" & vbNewLine & _
"End With" & vbNewLine & _
"End If" & vbNewLine & _
"End Sub"
Windows("Workbook.xls").Activate
ActiveWorkbook.VBProject.VBComponents(sht.CodeName).CodeModule.AddFromString shtCode


And this line is the trouble spot:

"Set myShape = Me.Shapes(Select Row then Click here)" & vbNewLine & _


The macro does not want the quotes around Select Row then Click here but they have to be there in order for the code to compile when it is written into Sheet1.

Any ideas on what to do regarding the quotes?

And thanks again for showing me that link!!

 

[erik.van.geit]

please use CODE tags when posting code: that will make your posts more clear and you will have more chance to get response

try this: double up the quotes

"Set myShape = Me.Shapes(""Select Row then Click here"")" & vbNewLine & _

(didn't test, but that should work)

 

[channel7]

Woohoo!!!!!

Double quotes smoothed it out!!!!

Thanks so much for being patient with me as I tried to learn exactly what terminology to use to explain my problem. As for the code tags, I didn't see them offered where I could insert them around my pasted code. For future posts, is the correct code tag format code<> blahblahblah code < / >?

 

[channel7]

I see now, I am to use [ code ] and then [/code]

 

[erik.van.geit]

[ Code ]
[ /code ]

use without spaces

you will find it easier to edit your posts when using the "advanced" screen

click User CP / Edit Options / Miscellanuous: check "advanced"