I created a shape (rectangle). I assigned a macro to it, to activate another worksheet.
Can I fix the position of the shape on the screen, near the top. I have 700 rows in the worksheet and I want this rectangle always visible, near the top.
I am trying to cut and paste an object (an excel shape to be more precise) into its same position. It could, for example, be relative to the top left corner, but how to modify the paste method will be welcome. Surely this is a piece of cake for most of you, but I haven't been able to find the answer for objects floating on the screen.
View 5 Replies View RelatedI'm trying to write some VB to draw a line shape and then move it across the screen. I have managed to draw the line and have used the following code to move it:
Set linetest = ActiveSheet.Shapes(1)
For iCount=1 To 100
linetest.Left = linetest.Left + 1
linetest.Top = linetest.Top + 1
Next
i) I can only actually see the line move if I run the code using the debugger and step through the loop. If I just run the code then I only see the end position of the line (note, this is not an issue with the calculation being too fast for Excel to display the line. I tried the code with a delay loop in it and it made no difference). I can get it to work by calculating the active sheet for each iteration of the loop, but this means I have no control over the speed of the movement; incidentally it moves very slowly).
ii) This seems a poor way to move the line. Is there anyway to move the start and end node of the line rather than using the left, top, width and height properties (i.e. can I get the old coordinates of the points and set them to different numbers)?
I would like to end a macro with a specific cell (e.g. E50) in the first cell in the upper left hand corner of the screen. How might I accomplish this?
View 3 Replies View RelatedI have an autoshape on an sheet, and use this code (it's a circle), to rezise it....
[Code] ....
However, it does rezise it, but the shape doesn't keep it's central position. Is there a bit of code I can add to stop the shape from moving? In other words, have the shape rezise around it's center of location?
I'm messing around with two shapes, a Cannon and Cannon Ball. With code the Cannon rotates for the angle of fire. What I can't do is position the Cannon Ball at the end of the Cannon when Cannon is at an angle. Long time since I was in school so forget most of my maths.
Code:
Sub IamUseless()
Rem this is work in progress and in NOT WORKING!!!!!!!
Dim xDistance As Double ' is inital x position of object
[Code].....
i need to loop through a column of values and get each value
Dim LastRow2 As Long
Windows("SCFOutput.xlsm").Activate
Sheets("Q2SCNeg").Select
Columns("A:B").Select
ActiveSheet. Range("A65536").End(xlUp).Offset(1, 0).Select
LastRow2 = ActiveCell.Row - 1
For Each c In Worksheets("Q2SCNeg").Range("A2:A" & LastRow2).Cells
MsgBox ActiveCell.Value
Next c
i did this but it selects the first blank row (row 15) and gives me an empty message box 14 times (which is right, but i need 14 values) the reason i need the loop is because the number ov values will always change
Is there a way I can position a message box on the screen? Is it possible to put in the coordinates?
MsgBox "Do you want to print this", vbYesNo
I have a Msg Box in my code. It usually pops-up in the Center of the screen. When I enabled a second monetor, the Msg Box now pops-up at the right edge of my main screen.
Is there a way to control where this Box pops-up? Ths code looks like this:
response = MsgBox("Switch to Manual Mode?", vbYesNoCancel, "Manual Switch")
If response = vbYes Then
WriteValue 0, "5MFWMODE.f_cv"
I want to resize a shape and set the height to a specific cell. How can I achieve that?
View 7 Replies View RelatedI have written a small piece of code that handles clicks on buttons on an excel spread sheet. When a button is pressed, i need to know the row number where the button resides in.
I have searched and read about this issue on several forums, and they all indicate that i need to use something like
application.caller.topleftcell.row
to get the row number and
application.caller.topleftcell.column
to get the column number.
However, when i use the row variant, it ALWAYS returns row number 1. When i use the column variant, it returns the column number it is actually in.
i have added the full code that is creating the buttons below, and also the part that displayes the rownumber that is incorrect.
Private Sub Workbook_Open()
Dim name
Dim time As Integer
time = InputBox("Typ the hour you are checking" & vbCrLf & "eg: 7, 10, 13, 14, 15, 16, 17, 18, 19", "Which Check")
I once used a Function to Measure the distance of the lefthand side of the active cell to right of the screen.
But now I can find it anywhere.
can anybody remember the function Name?
I'm using a bit of code (below) which is not amazing but which does seem to work, the idea being that entering a number then the active cell relating to that number will be highlighted and the sheeet position will "goto" it to move it into view on the screen. I have adapted this from something in one of my old Workbooks, and I admit it's probably a bit messy!
The problem is, it doesn't seem to always put the returned active cell in the same place, sometimes it's nicely central to the screen, but at others it's right at the top. As the numbers corrrespond to the top line of each little area of 30 rows, I'd like it to to come about 10-12 rows down. I can't seem to get this to work!
Code:
Sub FindSection()
Dim nm As Integer, txtnum As String, Found As Range
txtnum = Application.InputBox(prompt:="Enter a Number")
Set myRange = Worksheets("T&M SHEET").Range("K:K")
[Code]....
I have a very large workbook with multiple sheets and use macros to copy and paste all data as values, then delete some rows and columns depending on certain criteria. This starts at cell A1 and works right, then down.
This is then saved as a copy and distributed to a wide audience.
The problem is that when each page is opened up, the data shown is the bottom right of the whole sheet instead of the top left ( Panes are frozen for row and column headers).
I have an image overlapping a range of cells. I want this code to scan the pixel at the center of the cell, find out what color it is, and set the entire cell to that color, for each cell. Unfortunately with the GetPixel function I have to specify the x and y coordinates in terms of pixels from the top left corner of my monitor. The "CurrentCell.left & CurrentCell.top" method is not giving me the right location (Should start at cell AA1). If you would like, open up the file, go to sheet 2, insert an image, and click "Generate Field from Image". I set the cursor to follow the current position so you can see that it is selecting pixels from the corner of my monitor rather than cell AA1. Is there a way I can calculate the screen position of the top left of cell AA1?
Domino App.xlsm
[Code] .....
i like to block scroling the screen upto A1:A31?
View 5 Replies View RelatedIs it possible by using Visual Basic coding to position an image on a worksheet so that no matter what the screensize is it will always appear in the center of the screen, a corner or a side?
View 9 Replies View RelatedI require a macro to enable a selected shape to be moved from current cell location to relative position but 1 row up. eg topleft address = A4 and shift shape to topleft address = A3
View 5 Replies View Relatedwhen i run the below code i get an error 438 'object doesnt support this property or method'
View 2 Replies View RelatedIs it possible to position a predetermined cell (e.g. A42) in the top left corner of the screen. (Not every screen users use has the same size)
View 3 Replies View RelatedI'm just looking to prevent users from using the print screen or the alt print screen buttons on the keyboard. I have this script that works if I use "39" (Right Arrow)but wont work if I use "42" (Print Screen Button).
View 5 Replies View RelatedOn the attached spreadsheet there is two irregular shapes. "Area1" & "Area2". I need to be able to determine which area the "ball" shape is located in. If the ball is in Area1 then "Multiply 8" gets the ball and "Multiply 9" moves to the blue cell "AH39". If the ball is in "AreaB" Multiply 9 gets the ball and "Multiply 8" runs to cell "S37"
I should be able to do the moving of the shapes using all the samples, its determining which area the ball is in is the problem.
In the real spreadsheet i will probably have around 10 different Areas.
Move Two Objects - Select Case.xlsm
do you know if there is vba code to lock formulas so someone sees but does not touch and the same for values see but cant change??
my sheets with formulas are SUMMARY and Sheet2
sheet with value is TABLE
I have D4=0.42. I thought this formula =IF(D4<0.38,"Thin",IF(0.38<D4<=0.48,"Good shape","Bad shape")) was supposed to give me "Thin" for D4<0.38, "Good shape" for 0.38<D4<=0.48 and "Bad shape" for the contrary, but it only gives me "Bad shape" when D4 is clearly between 0.38 and 0.48.
View 4 Replies View Relatedcode doesn't work
Sub test()
If ActiveSheet.Shapes.Name = ("five") Then
Range("B4").Select
Else
Exit Sub
End If
End Sub
I have a shape in Excel called Canada. I grouped it with another shape and want this new grouping to be called Canada. But when I enter Canada in the shape name entry box, it doesn't actually change it (as it thinks I am referring to the existing Canada).
I need something like Names manager, but for shapes, not ranges.
Is it possible to find out if the selected item is a textbox through VB?
Is there any way that I can get the name of the shape i just clicked to call a procedure?
I have 35 shapes in my Worksheet.
Each shape has a different name. Each shape has a macro procedure assigned, wich do almost the same procedure (with just small differences, depending on from wich shape the macro was called).
I want to make just one macro procedure and catch from wich shape the macro was called.
How do i Keep shape where it is no matter if i scroll up or down.
Sub KeepshapeStatic()
Dim ws As Worksheet
Dim shp As Shape
Set ws = Sheets("Sheet1")
Set shp = ws.Shapes("MyShape")
'How do i Keep shape where it is no matter if i scroll up or down
End Sub
i am trying to draw a shape (square or rectangle) using vba textbox's i have managed to draw 2 of the 4 lines.
i am stuck on the ShapeRange.Flip msoFlipHorizontal codeing.
i have attached a example of what iam trying to do.
the coorinates from the last line enterd are saved to sheet1 for use with the next line entered ...