Inserting code into Sheet1 using macro

channel7

New Member
Joined
Nov 1, 2007
Messages
47
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!!!
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

channel7

New Member
Joined
Nov 1, 2007
Messages
47
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

New Member
Joined
Nov 1, 2007
Messages
47
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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

New Member
Joined
Nov 1, 2007
Messages
47
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!!
 
Last edited:

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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
Code:
"Set myShape = Me.Shapes(""Select Row then Click here"")" & vbNewLine & _
(didn't test, but that should work)
 

channel7

New Member
Joined
Nov 1, 2007
Messages
47
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 < / >?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
[ Code ]
[ /code ]

use without spaces
Code:

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

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

Watch MrExcel Video

Forum statistics

Threads
1,123,329
Messages
5,600,996
Members
414,419
Latest member
JRDunya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top