-
Apr 13th, 2019, 02:03 AM
#1
Thread Starter
Member
-
Apr 13th, 2019, 03:40 AM
#2
Re: How to insert code inside a command button in Sheet1 and not in module?
Welcome to VBForums
Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.
-
Apr 13th, 2019, 05:44 AM
#3
Re: How to insert code inside a command button in Sheet1 and not in module?
you would need something more like
Code:
xlmod = xlworkbook.VBProject.VBComponents(xlworksheet1.Name).CodeModule
Ln = xlmod.ProcStartLine(objBtn.Name & "_Click", vbext_pk_Proc)
xlmod.InsertLines Ln + 1, "msgbox ""hello world""" & vbNewLine & "msgbox ""test completed"""
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Apr 14th, 2019, 07:05 AM
#4
Thread Starter
Member
Re: How to insert code inside a command button in Sheet1 and not in module?
Originally Posted by westconn1
you would need something more like
Code:
xlmod = xlworkbook.VBProject.VBComponents(xlworksheet1.Name).CodeModule
Ln = xlmod.ProcStartLine(objBtn.Name & "_Click", vbext_pk_Proc)
xlmod.InsertLines Ln + 1, "msgbox ""hello world""" & vbNewLine & "msgbox ""test completed"""
Thanks my friend for your idea. When I try those lines of codes, it shows an error:
Please help me to fix the error. Thank you.
-
Apr 14th, 2019, 08:02 AM
#5
Re: How to insert code inside a command button in Sheet1 and not in module?
i can not read the image
what error do you get
is xlworkbook a valid workbook object?
does xlworkshhet1.name return the expected value?
also you may need to change to ln + 2
please note, i spent some time testing this should work as requested, to insert the code into he module, at the correct location, but i do not in any way expect the click event to work when the button is clicked, which while you did not ask for that, it may be that is the result you are looking for
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Apr 14th, 2019, 10:10 AM
#6
Thread Starter
Member
Re: How to insert code inside a command button in Sheet1 and not in module?
Originally Posted by westconn1
i can not read the image
what error do you get
is xlworkbook a valid workbook object?
does xlworkshhet1.name return the expected value?
also you may need to change to ln + 2
please note, i spent some time testing this should work as requested, to insert the code into he module, at the correct location, but i do not in any way expect the click event to work when the button is clicked, which while you did not ask for that, it may be that is the result you are looking for
I got this error: Conversion from string "xlworksheet1.name" to type Integer is not valid. Error in line xlmod = xlworkbook.VBProject.VBComponents(xlworksheet1.Name).CodeModule.
-
Apr 14th, 2019, 04:13 PM
#7
Re: How to insert code inside a command button in Sheet1 and not in module?
it worked on my test, but you can try as posted in the previous thread
Code:
For Each xlmod In xlworkbook.VBProject.VBComponents
If xlmod.Name = xlworksheet1.Name Then Exit For
Next
note also you should avoid you application constant names (xlworkbook) as variable names as it may cause a conflict
if you are actually trying to give the button a click event, you should probably create a class for a button withevents
if there are to be multiple buttons then the same class can be used for all buttons by adding each instance to a collection
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Apr 15th, 2019, 12:55 AM
#8
Thread Starter
Member
Re: How to insert code inside a command button in Sheet1 and not in module?
Originally Posted by westconn1
it worked on my test, but you can try as posted in the previous thread
Code:
For Each xlmod In xlworkbook.VBProject.VBComponents
If xlmod.Name = xlworksheet1.Name Then Exit For
Next
note also you should avoid you application constant names (xlworkbook) as variable names as it may cause a conflict
if you are actually trying to give the button a click event, you should probably create a class for a button withevents
if there are to be multiple buttons then the same class can be used for all buttons by adding each instance to a collection
Thanks so much for your help my friend. Finally found an answer from this link: http://www.vbforums.com/showthread.p...5379071http://
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|