• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

User Form

G

GN0001

Member
I am exploring this code below, which chandoo has posted:


Sub move_Image()

Dim left As Integer

Dim top As Integer


'Define the position values


left = Range("Left").Value

top = Range("Top").Value


'Select the shape and position it

ActiveSheet.Shapes("Rounded Rectangle 1").Select

Selection.ShapeRange.top = top

Selection.ShapeRange.left = left

End Sub

==========================================================

When I hit the F8 to run the code line by line, when it comes to this line:

left = Range("Left").Value


It shows a message box indicating run time error 1104, Method 'Range" of Object _ Global' failed, and when I keep the mouse over the line, it says:

Range("Left").Value = " Method 'Range' of the object_ 'Global' failed "


How can I fix this error? Or what do I need to add to these lines to make the code run.

I appreciate your help.

Guity
 
Guity,


You need to define the named ranges for the 2 cells where you will store the Left and the Top values.


Once that has been done, you are then F8 the code and it will run fine.


PS: do not forget to put the top and left values in the cells.


~Vijay
 
Dear Vijay,

How do I need to define them? I have defined them in Dim statement, is there any other way to define them or do I need to define more names?

Thank you very much for the help.

Guity
 
Dear Guity,


Assuming the sheet is called sheet1


click on Cell A1 and then click in the Name box which appears as a drop down to the left of the formula bar.


Type the name "Left" and press enter


repeat the process for cell A2 and name as Top


now you can go and enter the values for these 2 cells and run your code


~Vijay
 
Vijay,

I did, but this time it showed error on this line:

ActiveSheet.Shapes("Rounded Rectangle 1").Select

stating Run time error with a number

It says the item with specified name wasn't found.

Thank you for all your help. If you think, it is hard to learn codes, after learning this I will give up the learning of macros.

Regards,

Guity
 
Guity,


I follow the never day die attitude... so i would not encourage you to give up on Macros...


When ever you insert a Shape object on your sheet; it is identified by a name.


The name for the object is on the Name box; which is on the extreme left of the formula bar.


You can give the shape your own names by typing them,


Go give it a shot...


And do not worry about macros; we have Chandoo, Hui, Luke M and myself (i am not aware if there are more on this site; so apologies to those who have been missed)


~Vijay
 
Vijay,

It worked. Can you believe it?


But regardless of what value I put in the cell (A1 and A2), the shape moves to the corner over those sales. Can you please explain about it?


I follow the same attitude. But time is limited and I think I can learn more if I focus on non-macro stuff, then someday I can come back to macros. Right now, after this form, I have to assimilate this to chandoo's code for user form in Dynamic Dashboard. This means at least 1 to 2 weeks, If I give that up, instead I can learn other materials.


Hui is a great man and he has saved my life in critical situations and I have learned a lot from him. In short, Hui and this website have changed my life. I like chandoo's website and I am heavily exploring it and I want to learn all the materials. I am grateful to you, Luke and chandoo.


Guity
 
Dear Guity,


The code is meant to move the object to the Top and Left positions that are defined by the end user; so if that is working fine I do not see any issues.


I was not able to follow your comment "the shape moves to the corner over those sales"


Guess, the object is moving at a location where you do not want it to go...


Just change the co-ordinates for the Top and Left if that is the case and let us know.


~Vijay
 
Dear Vijay,

I am sorry about the sales instead of cells...I don't know what is wrong with that I make many mistakes...


I mean when I put the value in cell A1 and A2, the shape moves there over the cells A1 and A2, the shape always goes to top, left and If I change the values from 1 and 2 to 1000 to 2000, shape will go away form my spreadsheet. Even if, I don't put any values in the cells A1 and A2, the shape now moves.I can't find the relation between the values and move of shape. But the shape always go to top and left.


High Regards,

Guity
 
Guity

The left and top refer to how many pixels from the left and top of the screen the left hand side and top of your shape will be

So 0 & 0 will put it up at a1

If your screen is 800 x 600, 400 & 300 will put it near the middle etc
 
Hui & Vijay,


I understood. Thank you very much. Now, if we want to have a user form in our spreadsheet, do we need to insert the object(user form) into our sheet and then add controls to it first, then write the VBA code for it, or do we need to create the user form with a visual basic code?


Thank you for all help.


Guity
 
Just create the userform in VBA with all the controls etc you need

You will need a simple button or macro with a userform.show command to display the userform

Then the userform and controls will allhave their own code to control there actions
 
You must log in or register to reply here.
Back
Top