Adding a button using VBA

[aretai]

I have a code that adds a simple button. I haven't pasted everything, but most important things (i.e. place where it breaks).

Please Login or Register  to view this content.

Line commented points to the error. I first get message can't enter break mode at this time and I press continue and get error application defined or object defined error - when I run it from excel (when I run it once again with debugger it says subscript out of range).

I've checked the code in another spreadsheet and it works. Another thing that I've noticed is that there is change in terms of active sheet between normal run and debugged run. In the first time a spreadsheet is selected that is right to the spreadsheet that I had another button that run the actual code. In the latter case a correct spreadsheet is selected where all the results are.

thx

[romperstomper]

The error when using f8 is normal. How are you running the code when you run it from Excel?

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[aretai]

The error when using f8 is normal. How are you running the code when you run it from Excel?

I'm pressing a button on another sheet called configuration. It performs some operations. Operations that may be relevant are:

1) It deleted some worksheets
2) It creates new worksheets together with the worksheet "Results" where the new button should be placed
3) It renames new worksheets.

Apart from that it performs some basic calculations

thx

and I fixed my previous post

[romperstomper]

What kind of button do you run the code from? Also, is there a reason for using an Activex button rather than one from the Forms toolbar?

[BBGDan]

I played around a bit with the code and this seems to be working fine:

Please Login or Register  to view this content.

[aretai]

As I said it works perfectly fine when I type it in a new workbook and run it. However it produces errors I stated before when I run it in my workbook with the other code. I've debugged the code however and there are no errors produced up to this point. This is really odd for me. I thought that maybe there is some sort of bug in VB or maybe it has to do something with the fact that the worksheets were created recently (maybe need to register them or something). But I really don't have an idea what could be wrong and how to fix it, that's why i asked this question here.

thx

[aretai]

What kind of button do you run the code from? Also, is there a reason for using an Activex button rather than one from the Forms toolbar?

I wanted to add a simple button on the spreadsheet. I need then to assign a subroutine to this button. That's all. How can I use one from the Forms toolbar instead of Activex? Is the one I used Activex?

thx

[romperstomper]

Like so:

Please Login or Register  to view this content.

[aretai]

Like so:

Please Login or Register  to view this content.

Yes this works. However I have some code assigned to my OLEbutton. Below is the line that assign the code to the button, however the actual code is much more.

Please Login or Register  to view this content.

Is there an easy way to convert this code to the macro or a sub? Apart from that I'm very curious to check what could be the cause for my problems. If anyone knows please let me know.

thx

[romperstomper]

Whatever is in strCode would be your macro.
Since you've already noted that the code works in another workbook, there is something about your workbook that is causing the problem. We can't see your workbook, so it's hard to help with that.

[snb]

You'd better not use:
Application.ActiveWorkbook
ActiveSheet

But address them unequivocally:
workbooks("Name.xls")
sheets("sheetname")

Are you sure the sheet in which you want to put this commandbutton isn't hidden, protected or part of a hidden workbook ?

If not, you should be able to add a button with

Please Login or Register  to view this content.

Besides, maybe it's easier to make a button manually and write the corresponding macro for this button. If the button's property visible =False the only thing you have to do in your macro is switching the button's visibility

Please Login or Register  to view this content.

[romperstomper]

You'd better not use:
Application.ActiveWorkbook
ActiveSheet

That depends entirely on what you are doing. If your code is in an add-in, for example, you frequently want to manipulate whichever workbook/worksheet is active.

[aretai]

Ok thank you for help. I'll try with this macro build from the string. I've noticed another thing that the button is actually created

Please Login or Register  to view this content.

It appears on the worksheet with the properties I have configured. Still the very first line that sets the obj causes the debugger to produce errors.

I have also checked whether the problem was with the protected sheet, but it seems no. I have also tried to add a breakpoint (brown marker) at the third line (obj.Top = 15) and it seems that I'm able to get there using CTRL+Shift+F8. However when I step through each line the problem still exist.

Thank you

[romperstomper]

You can't step through code that adds activex controls to a worksheet.

[aretai]

oic. In that case I think that the error raised may have been caused by the code after the button (since u can't step through the activex control).

thx

[aretai]

Is there any automatic conversion available? I tried the following code to change manually so that it works with a simple button, however it is error prone.

Please Login or Register  to view this content.

[snb]

Why don't you use the most robust method:
- insert the active-X controls manually,
- insert the corresponding vba-code manually.
- set the visible property of the active-x control on False
- put the instruction Oleobjects("xxx").visible in your code and all is set.

If you persist in the accident-prone alternative:
Avoid using 'activate' in VBA-code.

Please Login or Register  to view this content.

[aretai]

the problem is that the button needs to be added by the code and then assigned some tasks also by the code. It is rather hard for me to ask user to create a button manually each time he will want to perform these operations.

thx

[snb]

You didn't quite understand what I said in my previous post. Please read it again carefully.

[aretai]

You didn't quite understand what I said in my previous post. Please read it again carefully.

sry but you either talk too much slang or it is too advanced stuff for me?

1) insert the active-X controls manually - what should be code for that? Is it the one I used previously?
2) insert the corresponding vba-code manually - once again code. Shall I put it in another sub?
3) set the visible property of the active-x control on False - this I should be able to do once I know the code to insert the active-X controls manually
4) put the instruction Oleobjects("xxx").visible in your code and all is set. Does it mean I need to do this for all of my active-X controls in my code? The rest is working just fine - it is only this place that is producing errors.

thx

[royUK]

snb writes English not slang, you can't be bothered to fill your profile correctly. Entering Earth is not funny nor original, nor have you entered the version of Excel that you use.

[contaminated]

@ RoyUK
By the way.I saw in books that if you need to add many buttons and then assign a code to each of them, a single class module is used, instead of writing a script for each button separately. Why not write a small script in a class module. Moreover, I have never seen that someone suggested class module here.

PS: I didn't read all posts, I think that OP wants to create many-many buttons and assign a code each of them. Did I interpret properly?
PSS: I didn't intend to hijack the thread. So please do not close the thread because of me,

[romperstomper]

If you use ActiveX controls, you cannot create them and assign them to a class instance in the same sub (your variables will be reset as soon as the sub ends) - you have to use OnTime to schedule another sub to actually 'hook up the class. It also would need to be reset each time the workbook was opened.

Personally, I would have an existing macro, use Forms controls and simply assign the macro to all of them. If required, you can simply import an entire module at run time containing the macro.

[aretai]

snb writes English not slang, you can't be bothered to fill your profile correctly. Entering Earth is not funny nor original, nor have you entered the version of Excel that you use.

Well I thought it would be funny with the Earth, but if requested I can fill the details. I do have entered versions of Excel that I use "Excel 2003 and Excel 2007". I couldn't understand the snb's answer - and confused it with inserting the button manually (as shown by one of my previous posts). That's why I thought it was a pro excel slang or too advanced stuff for me that's why I asked for clarification.

[aretai]

If you use ActiveX controls, you cannot create them and assign them to a class instance in the same sub (your variables will be reset as soon as the sub ends) - you have to use OnTime to schedule another sub to actually 'hook up the class. It also would need to be reset each time the workbook was opened.

Personally, I would have an existing macro, use Forms controls and simply assign the macro to all of them. If required, you can simply import an entire module at run time containing the macro.

I've created another sub and named it SaveResults. I assign it to the button as follows:

Please Login or Register  to view this content.

The button is created, however when I click it it displays the error:

Cannot run the macro "Myspreadsheetname.xls'!SaveResults". What is wrong?
thank you

[romperstomper]

Where did you put the SaveResults code?

[aretai]

Where did you put the SaveResults code?

it is placed just below the code that was creating a button. The sub that created the button is ended and new sub is added

Public Sub CreateButton()
'assign SaveResults
End Sub

Public Sub SaveResults()
End Sub

[romperstomper]

The macro you call should be in a normal module, not in a worksheet module or in the ThisWorkbook module.

[aretai]

oic. Yes I've moved it to a module. It seems to work fine. Thank you for solution.

[snb]

But if the macro is in another module you have to refer to that module

macro test1 in sheet1
macro test2 in Thisworkbook

Please Login or Register  to view this content.

in module sheet1

Please Login or Register  to view this content.

in module ThisWorkbook

Please Login or Register  to view this content.

[aretai]

Actually when I placed it in a standard module I can refer to it straight away