Is 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 RepliesI 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 display an image ("image") on an image control ("Image1") placed in a worksheet ("Sheet1"). The problem is that I want this action to take place when I press a button in a user form. I am trying to use the following code, but it seems that VBA does not recognize the image control ("Image1") in the worksheet.
Private Sub cmdDisplayImage_Click()
Dim image As Variant
image = ThisWorkbook.Path & "sun.jpg"
Sheets("Sheet1").Activate
Image1.Picture = LoadPicture(image)
End Sub
In one column I want to reference the maximum value entered in another column. In a third column, I want to refernce a different row that is in the same column as the maximum value.
View 8 Replies View RelatedI have a bit of a complicated one here so I have attached my book, its probably easier to undertstand the query by looking at the book. Hopefully somebody can help me out here.
I have 2 worksheets:
A results worksheet - this contains all category of results, each category has been given a specific sort id.
A foreign worksheet - this contains all categories found on the results worksheet with sort id = 7.
Now the complicated bit. The whole exercise is too pick up the correct exchange rate for the foreign category (sort id 7). each foreign type has a specific number at the end of its description in brackets e.g (3).
At the bottom of the "results worksheet" is a key which says what type of exchange rate to use for this type e.g. (3) Price of CAD 22.9 converted using exchange rate of CAD 1.9645 = £1.
What I need to do is on the foreign worksheet, it to recognise the number in the item description, keep it in mind then switch over to the "results worksheet" find the keys at the bottom of the page (the key is not necessarily in the same place all the time!!), match the number in the items description with the corresponding key and then find the exchange rate.
As i said very difficult to explain, since I reletively zero experience with this kind of work in excel. Luckily I been muddling my way through a work project gradually thanks to a forum member here. so i hope we can keep this up!
edit:
two restrictions in the way this solution can be done:
1- Results worksheet can not be modified, but it can be referenced to using INDIRECT, and you can make any number of modifications outside of the results worksheet.
2. Any solution must be automatic.
/edit
I have attached my workbook and highlighted the bits I need to fill and the picks I need to pick up.
Would be ever so grateful if some1 could show me the light here.
Part of my code adds a Worksheet to the selected Workbook and then re-orders them alphabetically, but one of the Worksheets (Called home) should always remain at position 1. Is there a way to dictate exactly which postion it should be in so that I can place it there after the sort is complete?
View 7 Replies View RelatedUsing excel and Crystal Ball I generated a workbook that calculates via VBA Value at Risk and diverse financial charts and ratios.
I have a second workbook, the template, where this values are pasted generating a report.
Up to this point the macro works correctly, the problem is that some of the Crystal Ball charts are pasted as images and I dont know how to "handle" this images.
I reached the point where I can paste the image in the template.
I want the macro to:
1) Detect the images in the template worksheet
2) Determine a position and size for each image
I need the cell address where the cursor is (in a non active worksheet).
View 5 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 have code written to auto save the active worksheet when a button on my user form is pressed. What I would like to know is if there is a way to lock the worksheet when the button that triggers the save event is pressed behind the scenes so the end user has a more difficult time editing the sheet. I know they can just resave the document and the protection will be gone, but for minimizing user manipulation.
View 2 Replies View RelatedI need to lock a worksheet tab (Main menu) in place so that it is always visible. There are multiple tabs that are all linked to the main menu by hyperlink. When work is finished in a worksheet I want the main menu tab to be visible and usable rather than using the scroll functions to get back to the main menu.
View 9 Replies View RelatedAdd Multiple Buttons That Select Row Of Housing Cell. One of my Macro's places a button on a sheet using Top/Left;
Set sbut = Sheets("Listings").Shapes.AddFormControl(xlButtonControl, _
Range("J" & count).Left, Range("J" & count).Top, 50, 12)
When I try to find the row of the clicked button using the following code;
Range(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Address).Activate
The code behaves differently in Excel 2002 and 2007. In 2002 it returns the row the button sits on, but 2007 returns the cell above the button. This could be because the workbook is an XLS running in Excel 2007 in compatibility mode? Is there a way to "nudge" the button down a little after it has been placed using .top/.left ? Or is the only way around this something like;
Range(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Address).Activate
count = ActiveCell.Row
If Application.Version >= 12 Then
count = count + 1
End If
Is this a bug, or something that I am doing wrong ? The workbook needs to be a .xls to ensure that it works in older versions of Excel.
I have an excel workbook we use for quoting jobs. All our pricing references are in another workbook on our network. I have reference in the 'quoting' workbook that refer to the 'pricing' workbook.
The trouble is, when some users open the 'quoting' workbook, and have another Excel file open, some references to the 'pricing' workbook change to the other file they have open.
I suppose there is some sort of replicable condition that is happening, but I can't figure out what is going on or why.
So my question is: Can I ' lock' an in-cell reference to an external workbook so that it NEVER changes?
I tried hand entering the reference (network location+workbookname+sheetname) into a cell and then referencing that cell, but I couldn't get that to work.
I need a macro that will create a new blank worksheet, move it to the last spot and paste the entire contents of the sheet named "master" into the newly created sheet.
I have tried to do this with the recorder but have been unable to get it to work.
So I am trying to create a budget worksheet for past and future expenses, basically an excel checkbook with running balance
In Column A I have my debits/credits
In Column B I have my running balance so that there is a current total next to each of the debit/credits in column A, it is formulated as follows, it adds the cells directly above it to the cell directly to the left of it (i.e. Cell B10 = B9+A10, which was dragged and iterated to many cells below)
I wan to be able to drag in drop cells in the A column (Just moving them up and down different cells in the A column) with out it changing formulas in the B column.
I tried the $ trick so that for example B10 = B9+$A$10 but if I moved cell A9 to say A10, I get a reference error in cell B10 (why is it doing this when B10 is locked with $)
I then tried going to format and locking the B column and not the A column and then protected the document but I still get the same REF error.
I know I can copy and paste with out effecting it but this is tedious, the whole reason I did this was so that I can move around bills EASILY and play around with what get paid when. There has to be a way to do this, but I'm probably searching with the wrong keywords.
It is possible to lock the Author of the Worksheet in File --> Properties --> Summary Tab..?
I am using Excel 2003 & 2007..
I have a worksheet that is protected with data entry cells unlocked.
1. Unprotect worksheet
2. Lock all cells in the worksheet and then protect.
3. After a date in a cell in the worksheet.
I'm a newbie to vba and at least know vba is the only way. And this need to be automated.
I have a worksheet that, unless a form for data entry is activated, should be read-only for columns 'A' thru 'M' and 'O' with column 'N' left editable.
The form is activated by a button with a macro assigned to enter the data in the above columns when the form is closed. This has been compiled and works nicely with the sheet unlocked, however, as i need certain columns to be read-only unless the form is activated, i'm stuck with leaving the whole sheet unlocked for editing with or without the form being activated.
Is there any way a VBA code could be used to unlock the worksheet columns that are read-only when the form is activated, and then lock them once the form data is entered to the worksheet and the form closed?
How to Lock Individual Cells in a Worksheet excel 2007 .... i.e.
A2:A8
I want this selected area locked with password.
I have an image box on a userform in Excel VB. Is there ANY WAY to load an image into this image box from an object that I have loaded into an excel worksheet something like
If userform1.checkbox1=True Then
userform1.image.picture = loadpicture (Worksheets("Sheet1").shapes("Object 1"))
ElseIf userform1.checkbox2=True Then
userform1.image.picture = loadpicture (Worksheets("Sheet1").shapes("Object 2"))
Else
msgbox "No image"
Endif
I need to link an image to a cell value and display the selected image in a new sheet of the same excel. I am attaching samplefile for easy reference. The requirement is, from the workout sheet when I select from drop down list, then the corresponding cell value appears in Parameter sheet in C9 cell.
There are four images copied in Parameter excel. The given image has to be selected and linked to the C9 cell value. Based on the selection that particular image should be shown on Sample Picture sheet. Here based on the selection only the image should reflect here.
I need to find an easy way to locate an image on my server and upon selection rather than inserting the actual image I need to insert the image path. e.g. "s:imageshighresgreen1234.jpg"
View 3 Replies View RelatedMy workbook simply has multiple columns of data which will be summarized visually in a tag cloud (I have the tag cloud part taken care of already creating either a jpg or png, I simply have to change the extension within a batch file to change between picture formats)
I need to have the tag cloud image display and update depending on which column the user would like to summarize (for now I have simplified the issue by only having one of the data columns in my workbook, and will worry about the user selection of the data column to be summarized later).
My Main Question:
Focusing on the image display and updating now, whether it be an ActiveX Image Control or any other way. I've been playing around manually before I try and code it and I can not get either a jpg or png file to attach to the ActiveX control (both of which cause an Invalid Picture Message).
The file name can stay the same if need be and for now I think/assume that'd be easiest (I have my Tag cloud generator just replacing the previously existing image file of the same name).
Would ActiveX control be the way to go for this dynamically changing image?
I am looking for a way to take the picture name associated with an image ie: (picture 571) and place that picture name in the cell next to the image.
I can see the picture name - it registers in the top left box that usually indicates the cell the cursor is on... and I could manually type that name next to each image.
However my spreadsheet has several thousand rows- each with several columns of information and an image..
I am looking for a way "formula?" to automatically read the image in cell J4 and populate cell K4 with the image name... and so on.
The reason for my request... is I am importing products into a web based shopping cart... to do so I have to export the products to a csv and import that csv into the shopping cart. The process of exporting to a csv- strips away all the images and leaves just data.
I found that I can save the document as an html and that process creates a folder with all the images- saved as (picture 571.jpg) if the resulting csv had the image name... the shopping cart would then look for that image name in my image file and it would populate into the shopping cart appropriately.
This seemed on it's face like a really simple thing to do, I have plenty experience with excel & I thought I'd be able to make cell k4= j4 and tada it would glean the image name & I could simply copy that formula all the way down the sheet & it would be done... but I just cant find the magic button that freely gives up the image name...
Is 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 have a dataset that includes the path and optionally an HTTPS URL to an image. Is it possible to embed this image in a cell?
Short of that what might be some other options?
is there a way to load an image out when u type the image name in the textfield.
Example when I type in my name it load my image out beside the name.
I have a number of image controls on a user form, each being loaded with an image from file.
Image1 being loaded with a picture of a bus image2 being loaded with a picture of a school
And so on
Now what I am trying to do is when that image is clicked the name of that image is passed to a textbox
So the action would be
1 image of bus loaded into image control1 , the name of that image being bus.jpg
2 image is clicked
3 the name bus.jpg appears in a text box
Here is what I have so far
Code:
Private Sub Image1_Click()
DataInput.TextBoxItem.Value = bus.jpg
DataInput.TextBoxItem.Value= Image1.Name - this does not work
End Sub
Which works but as you can see this is static, as I have supplied the value , were as I would like the clicking action to pick up the name of the jpeg being displayed
So expanding upon this if say image1 is loaded with say a picture of a car instead of a bus
LValue4 = "E:Car.jpg"
Image1.Picture = LoadPicture(LValue4)
Now when it is clicked the name of the image being displayed in image control1 would be Car.jpg
And it is this value I wish to transfer to a text box for now will do more with it later
In addition to this I am not sure if the best idea is to have a number of image controls and load each with an individual picture or have one image control and swap the picture by clicking a command button
Is there a way to have image box display an image with a name matching data from a cell? example: if I type "hello" in cell a1, Image box will load image named "hello". And display new images by changing the name in cell a1. Note: the images will be located in a permanent folder.
View 3 Replies View RelatedI just converted from Excel 2003 to 2007 and print previewed a worksheet and find a tiny image with a small portion of the print area. When I look at the sheet in page break preview mode, I see the 8.5 x 11 sheet broken up into approx 77 smaller sheets. When I try to move the page breaks to include the whole sheet it states the change cannot be made as it will result in an image less than 10%. I have attached two images - the first shows the print preview I get, and the second show the multiple pages when I view in page break.
View 4 Replies View Related