How to move the Command Button having a macro attached to it to a different location/position on the excel sheet using Aspose Cells

Aspose.Cells Product Family
1

Hi,

We have a requirement where we need to show a message box when user clicks on the command button, for this since aspose doesn't support creating/handling macro's through code we have added the command button and handled the click event (Showing a Message Box) and saved it file as a .xlsm (Say Test.xlsm), we are using this as a template file. we are writing all the values to this template file and saving back with a different name(Say Test1.xlsm), the macro(button click event) is getting saved to the new file and the message box is being displayed when this button is clicked, it works fine till here.

In the requirement we also need to place this command button a particular location, and this location is dynamic, ie depending on the input this file gets, we need to place the button accordingly ie some times at range(20,1) and some times at range(40,3) and even at times hide this button as well.

While debugging we observed that when loading the template file Test1.xlsm, the command button is getting loaded as a picture, so when I am using the .Move or .MoveRange properties of the picture or shape it doesn't move to a different loaction/position, the command button looks like it is fixed at a location that is specified in the template. I am using the below code

Dim wbCopy As Workbook = New Workbook()

wbCopy.Open("C:\\Test1.xlsm", FileFormatType.Excel2007Xlsm)

'C:\\Test1.xlsm is having a Command Button with an handler for the click event, ie it shows a messagebox "hello" when clicked on the button and this button is placed on " A2 "cell

Dim wsCopy As Worksheet = wbCopy.Worksheets(0)

wsCopy.Name = "xxx"

wsCopy.Pictures(0).IsLocked = False

wsCopy.Pictures(0).Move(35, 0)

wbCopy.Save("C:\\Test2.xlsm", FileFormatType.Excel2007Xlsm)

Can you please let me know if I am doing any thing wrong in the above code or please suggest me a solution to move the command button to a different position and it should also retain the click event?

Thanks & Regards,

Padmaja

2

Hi,

I think you my use Shape.UpperLeftRow and Shape.UpperLeftColumn attributes for your need. Also, you may use Shape.MoveToRange() to move the shape to your desired location in the worksheet.

I have tested your scenario with a dummy .xlsm workbook that has a button (attached with macro) into the A1 cell in the first worksheet. I moved it to A10 cell in the worksheet. Following is my sample code:

Sample code:
Dim wbCopy As New Workbook()
wbCopy.Open(“e:\test\Book1.xlsm”, FileFormatType.Excel2007Xlsm)
'.xlsm is having a Command Button with an handler for the click event, ie it shows a messagebox “hello” when clicked on the button and this button is placed on " A2 "cell
Dim wsCopy As Worksheet = wbCopy.Worksheets(0)
wsCopy.Name = “xxx”
'Move the button to A10
wsCopy.Shapes(0).UpperLeftRow = 9
//or
'wsCopy.Shapes(0).MoveToRange(9,0,10,1)
wbCopy.Save(“e:\test\outBook1.xlsm”, FileFormatType.Excel2007Xlsm)


Thank you.

3

Hi,

Thanks for help, I have tried with the options you have specified, but still unsuccessful in moving the command button, hence I am attaching the sample application along with the workbook I am using, can you please look into this issue and let me know the right way to move the button to a desired location, or could you share the demo application.

Also please let me know if there is a way to remove/delete this button from the output excel file before saving to the disk.

Thanks & Regards,

Padmaja

4

Hi,

Thanks for your project.

I have logged your issue into our issue tracking system with an issue id: CELLSNET-17568. We will look into your issue and get back to you soon.

Thank you.

5

Hi,

Can you please let me know if there is any progress for this issue?

Thanks & Regards,

Padmaja

6

Hi,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Please try the attached latest version of Aspose.Cells. We have fixed your mentioned issue.

Thank You & Best Regards,

7

Hi,

Thanks for the updated DLL, it worked for moving the button to a desired location, but still I have another issue I have a requirement which should move the button to a location on a condition and on the other condition remove/delete the button, The first requirement is solved by the DLL you have provided but the second requirement is still not met, I have tried using the DeleteShape and also RemoveAt properties but still the button appears if I open the workbook.

I am using the below code on the button click event where the workbook "Book2" is having a command button on it:

Dim wbCopy As Workbook = New Workbook()

wbCopy.Open("C:\\APPS\\GCBCode\\Phase1\\Sample\\ButtonMacroSample\\Book2.xlsm", FileFormatType.Excel2007Xlsm)

Dim wsCopy As Worksheet = wbCopy.Worksheets(0)

wsCopy.Name = "xxx"

wsCopy.Cells(10, 2).PutValue(CDate(x).ToString("d/MM/yy"))

wsCopy.Shapes(0).MoveToRange(20, 0, 21, 1) ' This method is working now with the new DLL

wsCopy.Shapes.DeleteShape(wsCopy.Shapes(0))

'wsCopy.Shapes.RemoveAt(0)

wbCopy.Save("C:\\APPS\\GCBCode\\Phase1\\Sample\\ButtonMacroSample\\Book2.xlsm",

FileFormatType.Excel2007Xlsm)

Can you please let me know the right way to delete/remove this command button from the workbook, that will solve my issue completely.

Thanks & Regards,

Padmaja

8

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for the feedback.

We have found your mentioned issue after an initial test. Your issue has been registered in our internal issue tracking system with issue id: CELLSNET-17652. We will look into it and get back to you soon.

Thank You & Best Regards,

9

Hi,

Can you please let me know if there is any progress for this issue?

Thanks & Regards,

Padmaja

10

The issues you have found earlier (filed as 17568) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.

While trying to keep the API as straightforward and clear as possible, we have decided to recognize and honor the common development practices of the platform; we have re-arranged API Structure/ Namespaces.

With this release, we have reorganized the API classes for Aspose.Cells component. This change has some major aspects that we follow. We have added new namespaces. The entire API (classes, interfaces, enumerations, structures etc.) were previously located in the Aspose.Cells namespace. Now, certain sets of API have been moved to their relative namespaces, which make the relationship of classes (with their members) and namespaces clear and simplified. It is to be noted here, we have not renamed existing API (classes, enumerations etc.) but, I am afraid you still need to make certain adjustments in your existing projects accordingly.

For complete reference, please see the product's API Reference.

11

Hi,

Any update on the issue for removing the button(shape object) from the shapes list, this is the only feature left to be added in our application before releasing the product, can you please let me know the status by when can we expect the fix for this issue.

Is the new version released V5.0.0 having this issue also fixed in it?

Thanks & Regards,

Padmaja

12

Hi Padmaja,

Yes, please try Aspose.Cells for .NET v5.0.0, it also fixes the issue regarding removing the button shape.

Thank you.

13

Hi,

Today we have installed the new released version V5.0.0.0 and we are totally disappointed with the version released, the feature for Moving the button as specified in the release notes is also not working.

The last version of Fix V4.9.1.11 you have provides was working well and had the functionlaity to move the button by setting the "MoveToRange" or "UpperLeftRow" properties of the Shape object. The only thing that was not working for meeting our functionality was RemoveAt and DeleteShape properties, I requested for this functionality, and you informed me that it will has been added in latest version of release(V5.0.0.0), this version is having the RemoveAt and DeleteShape working but when we have used it in our application it is poping up errors if we try to open the excel after saving it from the code, but the other functionality I have asked for "MoveToRange" and "UpperLeftRow" stopped working. We didnot expect such a thing for you people.

I am attacing the sample appliaciton with version 5.0.0.0 and also the image which shows the error when we are trying to open the file which we have saved after using "RemoveAt" or "DeleteShape" properties

Can you please look into this issue and let me know if I am doing any thing wrong for while setting these properties or send the latest fix's dll ASAP?

Thanks & Regards,

Padmaja

14

Hi,

Yes, we found the issue regarding “MoveToRange” and “UpperLeftRow”, we have reopened your issue, we will fix your issue asap.

Sorry for any inconvenience caused!

15

Hi Amjad,

I am waiting for the fix, hope I will get it soon and this fix will be fixing both of my issues, Moving and Deleting/removing of the button(shape object).

Thanks & Regards,

Padmaja

16

Hi,

We found the issue regarding MoveToRange for the shape and fixed it too, but we could not find the issue regarding Deleting or Removing shape. Could you post your file with details that could produce the errors (you mentioned) regarding RemoveAt() method. We will check your issue soon.


Thank you.

17 
Hi,

We have fixed the issue regarding "MoveToRange" and "UpperLeftRow". Please try the attached version.

We remove the shape using "RemoveAt" or "DeleteShape" properties and it works fine. Could you post your output file here. We will check it soon.


Thank you
18

Hi Amjad,

MoveToRange is working now with the new version, but I am getting the error message still if we use the DeleteShape or RemoveAt in our code, I tried with a sample application, such an error is not coming in it, but getting in the reports generated by our application, we are extensively using CopyData, CopyStyle and adding n number of comments and hyperlinks to the cells, may be this is causing the error.

I am trying to reproduce the same error in the sample application and post it to you along with the output file.

Thanks for solving the MoveToRange issue.

Regards,

Padmaja

19

Hi Amjad,

I am able to reproduce the issue of DeleteShape in the sample application as well, please find the sample appliaction attached, the issue is coming when we add Comments to the cell and then try to delete the button using DeleteShape or RemoveAt.

In the sample application attached, I have opened a workbook using Aspose Open method say "Book2" which is having a button on it and then added 6 comments to different cells and used the DeleteShape method for deleting the button on the worksheet, now I have saved the file on the disk using Save method into another workbook "Book3" when we try to open the "Book3" using Microsoft Excel, it gives the errors which I specified in my previous post.

Can you please look into this issue and resolve it ASAP?

Thanks & Regards,

Padmaja

20

Hi,

Thanks for the project,

I can reproduce the issue as you have mentioned now. I have reopened your issue and we will fix your issue very soon.

Thank you.