Button Position/Coordinates On Worksheet. Discrepancy Between Versions

Apr 30, 2008

Add 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.

View 4 Replies


ADVERTISEMENT

Determine Position Of Shape/Button On Worksheet

Jun 2, 2008

I 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")

View 9 Replies View Related

Discrepancy Between Word And Worksheet Syntax?

Oct 15, 2009

I should disclaim that I'm quite novice at that, so my approach to writing macros is to record individual steps to determine the appropriate method, then copy and paste.

In any event, I have a macro that creates a word doc from content in an excel file. I copied over the code for inserting a text field from the Word Macro Recorder:

View 4 Replies View Related

Insert Row On Button Position

Feb 20, 2002

How do I insert a row using a macro button that insert the row beneath the row that the macro button is touching?

View 9 Replies View Related

Fill Range Based On Button Position

Jul 3, 2007

I'm trying to find out how can I fill a range of cells when clicking in a Command button in Excel. The process should be:

1) The user opens my XLS worksheet.
2) Goes to the next empty row and fill some specific fields
3) Then he should fill up 15 parameters on the right as Y or N
4) In case all of them are correct instead of going one by one typing "Y" I want he clicks in a button (with VBA code associated) and then 15 cells on the right should be filled with "Y" value

View 8 Replies View Related

After Clicking On Button, Move Cursor To Real Position Found Value.

Feb 18, 2009

how can i define, in vba that after click on button in sheet1, it looks at value in cell B1 in this sheet and move my cursor exactly on this value in sheet2 where i have data in range (B4:C6000).

View 2 Replies View Related

Geomean Calculation Discrepancy

Mar 12, 2008

For a set of of paired values A and B, I calculated the Geometric Mean two different ways in Excel (Office 2003). First using the GeoMean function. Second using the equivalent formula,

which is:
(A * B)^.5
(i.e. the square root of A times B).

I was surprised to find that the two methods produced different results 11% of the time.

OK, the discrepancy does not appear until after 10 or 11 decimal places, but I would like to know which method produces the more accurate result. Does anybody know?

calculating the geometric mean using logs (wherein the geometric mean of A and B is equal to the anti-log of the average of their logs) produces discrepancies about 30% of the time, so I assume that is the least accurate method.

View 9 Replies View Related

Fix Position Of Picture On Worksheet

Mar 22, 2008

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 Related

Discrepancy Report (logistics Field)

Oct 29, 2009

i am regular viewer of these forum..really this forum gave me many nice information. btw i am wrking for in a logistics field. i am not tats much professional in excel.

every year end i am preparing anual stock taking report in excel. but this time i like to take adavantage of membership of this forum by asking some some tips for this my anual stock taking report. so i am attached a sample file. in this file sheet-1 i put all datas of stock items. in sheet-1

A2=S.No
B2= Item Code
C2= Description
d2= UOM (Unit of messurement)
e2= Closing stock (stock in system)
G2= F2-E2 (Discrepancy)

now i want only Discrepancy (if there is minus and plus...)item lists in another sheet (report sheet). how can get all this data there?

View 5 Replies View Related

Worksheet Referencing And Position Recognition

Jun 4, 2009

I 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.

View 14 Replies View Related

Placing A Worksheet In A Specific Position

Oct 6, 2009

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 Related

Picture Name & Position Of Images In Worksheet

Mar 1, 2008

Using 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

View 4 Replies View Related

Current Cell Position For A Non Active Worksheet

Dec 15, 2009

I need the cell address where the cursor is (in a non active worksheet).

View 5 Replies View Related

Restrain/Lock Image Into Position On Worksheet

Aug 18, 2006

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 Replies View Related

Macro To Create Worksheet, Change It's Position, And Paste Date Into It

Apr 26, 2009

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.

View 9 Replies View Related

Position Cell In Top Left Position

Jan 7, 2009

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 Related

VBA To Recognize Xl Versions

Jun 3, 2009

I have some codes that require different values when running under xl2003 vs. when running under xl2007.

View 11 Replies View Related

Macro For Different Versions

May 4, 2009

I have been working on this spreadsheet off and on for a while now. I have a macro that works in Excel 2007 and a different one that works in 2003 (thanks to the help of Mr.Excel.) My problem is, the people who will be using this spreadsheet all have various versions of Excel. Is there a way to script this macro to work for all versions, or do I have to have a different script for each version of Excel? The one I am currently in need of is a script for 2002. See below for the script for the 2007 version. If there isn't a way to make a "one for all" solution, some assistance on a script that would work on 2002 would be great! The section in red is the section that returns the error when people try to run it with earlier versions of Excel.

ActiveWindow.SmallScroll Down:=-9
Columns("B:G").Select
Selection.Copy
Columns("B:G").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Columns("L:N").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("N11").Select

View 9 Replies View Related

Installing 2 Versions (or Office On Same Pc)

Nov 23, 2008

ive got my highly expensive office enterprise 2007 disk
what is the best way of installing this so i can still use 97 and 2007. any tips?
i wont bother with outlook but i'd like to try the rest ,mind you onenote,infopath and groove are a complete mystery!

View 11 Replies View Related

Multiple Versions - One Computer

Feb 13, 2009

I'm running Excel 2007 with Windows Vista. I also have Excel 97, which I was told would not run with Vista. I've since discovered that many programs that I was told wouldn't run with Vista seem to work fine.

Is it possible to install Office 97 on the same machine with Office 2007?

View 9 Replies View Related

Differences & Incompatibilities Across Versions

Aug 26, 2008

I may be creating a small Excel-based VBA application for a client on a semi-formal basis. I personally use Windows XP with Office XP installed, but I'm concerned about the possibility of serious differences between versions of Windows and Excel that may cause my code to fail on other machines.

I don't think I need to cater to any version of Excel BEFORE that in Office XP, but I probably do need to cater for versions after it. That's Office 2003, Office 2007 and Office for Vista,

View 7 Replies View Related

Store & Compress Old Workbook Versions

Mar 19, 2007

I'm looking for is ComplyXL and that costs $300 to buy. So, as a poor grad student, I'm looking for an alternative solution - either a freeware program or a hassle-free VBA code.

So here's what I need the code or program to do: When I ask excel to open a pre-existing file, I'd like it to automatically rename (& compress?) the last used version of the file & save that to a filename-specific archive somewhere (hidden?), before opening the file as requested. Alternatively, I suppose that a mirror copy of each 'just used' file could be created & saved to an archive folder whenever the used file is saved & closed.

--Note: to avoid making copies of EVERY workbook I ever use, I suppose there should also be a button in the toolbar or a file-associated setting that lets me identify which files I want this task to apply to...

Basically I'm looking to have excel automatically save versions of certain key files (like Word's 'Versioning' feature) without having to remember to 'Save As', without the versions taking up much space, and without having to store the older file versions in the same place as the most recent one.

And yes, I know that a similar problem has already been posted & solved here, but they didn't include a way to choose which files used versioning nor (if it's possible) a way to compress the archived versions to save space (maybe by adding it into a WinRAR archive somewhere?).

View 11 Replies View Related

'INDIRECT' Formula Across Excel Versions

May 22, 2009

I have made a file that works perfectly in excel 2007, but when I send it to a client it doesn't work as they have 2003.

View 12 Replies View Related

Ordered Table (compatable With .xls Versions)

Jan 4, 2010

The document shows a table on the left that puts the data from the table on the right into order by total (and if same, alphebetical by name). However the formula used to achive this is not compatable with previous versions of excel. Need alternative function that will bring the same outcome but work with pre 2007 versions (ie .xls file extention).

View 3 Replies View Related

XLS File Type Association With Two Versions

Apr 8, 2008

I am changing the file type association for .XLS to
C:Program FilesMicrosoft OfficeOffice11excel.exe

I'm using "Change" and "browse" to set it to that exact file. However it still starts version 12 (XL2007) which is dual installed with version 11 (XL2003).

I was successful in creating a shortcut that opens version 11, despite Microsoft's apparent attempt to thwart that; now I need to fix the file association. (Try to directly edit the Office links in your XP Start Menu - how the target is grayed out?

View 9 Replies View Related

Automatically Convert Older XLS Versions To Newer

May 30, 2009

I have a dir with over 150.000 files in it, especially pdf, doc and xls. Now I'm looking for a script or program that will automatically search the entire directory and look for old xls files, open them and save them in a new(er) version. I'm working with a software that needs to use these XLS files in my directory but it can't use the old xls-versions. It's an impossible job to search by hand for the old versions and put them in a new(er) version.

View 11 Replies View Related

Translating Argument Of A Function Between Versions Of Excel

Jul 2, 2013

When I attempt to send a spreadsheet using a German version of Excel, the following formula does NOT work properly.

=ZELLE("adresse",C14)

The function gets translated into =CELL("adresse", C14), and what I need is, obviously, = CELL("address",C14). The argument does not get translated.

View 1 Replies View Related

Design Spreadsheet In 2007 For Use On Earlier Versions

Jul 17, 2009

Compatibility mode, in 2007, is frustrating me like crazy. I thought it was supposed to 'limit' functionality to what was in 2003. But what it seems to be doing is letting me use features that only work in 2007and then tell me when I save that 'hey, these won't work after you save'. Ugh! I can't even pick a highlight color without getting a warning and wondering what it will look like to 2003 users!

Is there a 'theme' or anything that will truly limit the features I use to those available to 2003 users?

View 5 Replies View Related

Defining System Root On Different Versions Of Windows

Feb 22, 2007

I have to run a daily report from winxp or win2k and need to change the host files a few times for access.

I tried to use %systemroot% in the file path, but for some reason vba doesn't like it.

Here's the snippet I'm using for copying over the host file based on user choice: (the thing works if I change to the actual system root path (C: etc...)

Private Sub CommandButton1_Click()
Select Case ComboBox1
Case "- Choose -"
MsgBox ("This is not a choice")
Case "BTR"

View 9 Replies View Related

Remove X Close On UserForm. Multiple Versions

Mar 2, 2008

I've found a link on OzGrid "How to remove the (X) CloseButton from Userforms" but I can't seem to get it to work with Excel 2003. The code looks like it was written for Excel '95, '97 and 2000.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved