I'm using the following code to get the linked cell and return the value to a range. Is there anyway I can achieve this WITHOUT selecting the shape to get the linkedcell. I find using 'select' is cause me formatting problems.
VB:
Sub finalizeclrx()
Dim s As Shape, finRNG As Range, pk1 As String, SphFill As Long
SphFill = Worksheets("input").Range("K65536").End(xlUp).Row
Set finRNG = ActiveSheet.Buttons(Application.Caller).TopLeftCell
For Each s In ActiveSheet.Shapes
If Not Intersect(finRNG.Offset(-5, -11).Range("A1:A1"), s.TopLeftCell) Is Nothing Then
I would like to make one drop down list work with another. Is this possiable?
For ex: IF I have a list car companys in one list, I would like for me to select a certin brand in one list and all the models of that brand appear in another.
I am trying to set up a spreadsheet with linked dynamic drop down lists, based on a table.Using the OFFSET function, I have managed to get this to work if the table is on the SAME sheet at the drop down lists. But I want to be able to put the drop down lists onto a different sheet, so that I can lock and hide the table.
I don't want to use the 'name based on the previous column' idea, as the table is large and will change weekly. the drop downs show all values, even duplicates, which I would like to get rid of.
I've defined a number of variables in my spreadsheet. When I want to use one I type "=variablename" and 'm presented with a list menu.jpg
In the above example, I have several variables starting with the word "Harvard" (a town, not the university :-)
How do I select one of these without having to double click with the mouse.
Up and down arrows allow me to highlight an item but I cannot find a keystroke that allows me to select the highlighted item. Instead I have to lift my hand off the keyboard and use the mouse.
I have a spreadsheet which contains our fleet information covering many vehicles in one list. The teams for these vehicles are listed in Column A. What I would like to have is:
A dropdown box to pick from All, or the various teams in A1To hide every row that doesn't match the selection of A1To reveal every row that contains the selection for A1 at any point in column A. e.g: "*Dog Team*"
This is because something might be listed as "Moved from Dog Team" or "Dog Team, temporarily on loan", or "Dog Team V66.m"
I have taken a small sample of the data I will be working from and made a very simple drop down box which displays the pages of information for the vehicles by hiding/unhiding blocks of columns:
Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$2" Then If Range("A2") = "MOT/Service" Then ActiveSheet.Columns("A:P").EntireColumn.Hidden = False ActiveSheet.Columns("Q:CE").EntireColumn.Hidden = True ElseIf Range("A2") = "Contact Details" Then
[Code]....
But because there is also filters on the sheet, meaning the positions of the entries based on rows can change, I need it to search for the "*xyzzy*" method, but am unsure how to do this! While just using the Filters will work, due to some human limitations I have been asked to have a specific drop down box in a very specific location with instructions next to it.
I have this worksheet with 2 buttons and 2 textboxes.the first button is named search literally to search the items inputted in the textboxes and the second button is clear which clear all the inputted items in the textboxes and the search field. I manage to code those things however I have a main problem. How am I suppose to linked a checkbox with those items?
For example I searched this particular item so the checkbox corresponding to that item must show up in the left most column of it. and if I searched multiple items those checkboxes must show up too. I am having a hard time coding and trying since I am new in vba. I also attached a sample worksheet. I am using excel 2007.
I am trying to create conditional formating of a cell based on the value of another cell whish is linked to another sheet. I an using the "The formula is =$AA$30=1" where the cell AA30 is the cell linked to the other sheet. the problem is I have to do thiss for approx 200 cells ie =$AA$30=1, =$AB$30=1, =$AA$31=1, =$AB$31=1 --- =$AZ$42=1. Do I have to enter the formulea for each cell or is there a way to automate this?
The problem I am trying to solve is to have a cell return a cell value based on another linked cell. The cells I want to link are in separate tabs or could be another workbook.
For example if cell A5 in sheet2 is linked to A20 in sheet1...A5 has formula(=Sheet1!A20). I want the next cell B5 to look at A5 and return the value of cell Sheet1!D18.
So in essence I want the second cell to look at the first cell and return a value that is 3 columns over and 2 rows up.
I have several excel documents all linked to one unique excel document which I will call document A.
Document A is a performance spreadsheet where I have percentages, sales figures and times, for my sales team.
Each sales person has their own unique dashboard (another excel document) that I am currently trying to finalize.
Their personal dashboards have pie charts with text boxes. The text boxes within the charts link up to their relevant cells in document A.
When Document A is open and I open 1 of the sales person's dashboards. Everything looks good and works, however.
When I close document A the dashboard information changes. For example, the dashboard information could be showing 25% but when I close document A it reverts to 0.25
If I close down the dashboard and open document A back up, change it to 30% then close it. The dashboard recognizes the change BUT reads as 0.30. This is the same with them all.
The only fix I have figured out is creating another tab in Document A then duplicate of all the information required for the dashboards then save the cells as TEXT (as opposed to currency, percentage etc).
This kind of defeats the object because I have formulas set up in Document A and don't want to be doing twice the work.
I have to fill out two cells (lets say A10 and A11), with the same data taken from a list of given data. The given data is placed in one column.
I have created Combo Box where the ListFillRange is the given data area and in the LinkedCell I tried to write two cells but Excel doesn’t accept list of two cells. For example, I tried to write in the LinkedCell A10,A11 and A10:A11 and A10;A11 but none of this syntax worked.
I can’t write A10 only in the LinkedCell and then to write a function A11=A10 in cell A11, because in some cases the content of cell A11 will be changed by another Combo Box linked to cell A11 and that Combo Box will override any function that will be written in A11.
I have several cells in my spreadsheet A which are linked to cells in another spreadsheet B in the same workbook. My problem is that the cells in spreadsheet A only display the link formula in the cell and not the value of the cell in spreadsheet B which it is linked. I have several other cells in spreadsheet A that are linked to cells in spreadsheet B and they display the values correctly.
I am writing some VBA code in Excel 2007 and am stuck on a property with the standard form check box (not OLEObjects).
I would like to check and find out what the value is of the linked cell for a checkbox...
Like, if a user clicks the checkbox, it changes the linked cell value to true or false... But I cannot figure out how to "lookup" or "go and get" this true or false value through code...
I have a master summary sheet that contains all the required information for 21 other worksheets. Each row contains the information for a single sheet. I have linked all the relative cells to the first sheet but would like create a formula that changes the row reference in each of the links rather than the tedious task of updating each individual link in every sheet.
I have tried using the indirect function but constantly get errors returned, the basic formula i have been using is: =INDIRECT("Summary"!C&,X1)
'C' is the column in the summary sheet that the information should be taken and 'X1' is the cell in the worksheet that will define what row the data should be taken e.g in one sheet the reference for that specific cell will be C5 and the next C6 and so on.
I have a worksheet, and I want to place a combobox in a cell, but being able to add values not on the corresponding list. Done almost everything, but cannot find a way to use the position of the cell, so I can dinamicly place a combobox. Ex.- place a combo in a cell, then after you get the value, delete the combo and put it in the cell bellow, etc. In order to do this I need the position of the cell: left, right, height, width.
Sub randomnumbers() Range("B1").Formula = "=randbetween(1,100)" Range("B1").Value = Range("B1").Value End Sub
There is a button that i click and it creates a random number to cell 'B1'. it works fine, but i need to lock the document so that the user can only access one cell... 'A1' when i lock the document, and i click the button, it gives me a '400' error. i think it is because the button points to a locked cell.
I have attached an example spreadsheet to describe what I am working with. I was wondering if there is an easy way to have it so that everything in Column A is hyper linked to the ship bill file. This list can exceed over 1000 and to hyper link each line is too much, is there an automatic way to do this? For example. I would like the value '00001 in cell A2, to be hyper linked to the file "Shipping Bill - 00001" So that I can just click on the link and open up the file. The shipping bill files are in the same folder as the example spreadsheet.
I need to copy and paste data from the internet into Sheet 2, but it's not in the correct order, so I must swap column A and column B in Sheet 2 only. The problem with this is that when I swap them, the linked cells swap as well, which destroys my data on Sheet 1 columns C-Z.
Is there a way that I can keep the cells on Sheet 1 completely static, so that it only reads the data I put into the corresponding cell on Sheet 2?
Or maybe, is there a way I can just swap the cell information in Sheet 2 without messing around with the formatting? Just a complete swap of A1>B1 and B1>A1?
I have a golf teetime reservation spreadsheet. For each tee time there can be up to four players names so I use 4 columns. Times are listed in rows every 10 minutes beginning 6am until 5pm for a total of 66 rows per day. I have a member/customer list that I have on a separate sheet that I've named. I am using the ActiveX combo box next to each possible entry (so 4 columns times 66 rows = 264 combo boxes). It's a little slow and takes a long time to open. I like the ActiveX combo box because it allows me to define the box format and display and it allows search/read/write capability. The idea is the customer calls, I ask what time they want and we arrive upon an available time. I ask for a last name, I click the combo box, type the first letter of the first name and then scroll if necessary to the name and click it to add the name to the time/player cell. If no name in my list we simply type it in and I'll capture it next time I refresh my customer list.
way to dynamically define the linkedcell in the properties so that instead of 264 combo boxes, I might have one combo box that I can click on and then direct my selection to the cell I want it to go into.
I am using the Format as Table feature in 2010 and I am summing a range of cells (C2, D2 and E2) with the result in F2. I have linked F2 three rows below the table in cell C5.
When I add new data in the 3rd row in the table feature I can get an updated result in F3 but my linked cell does not update as it is now pushed to cell C6.
How can I have the linked cell update with the new total from cell F3 as it moves relative to the table?
I need my code to start at the cell that the checkbox is linked to, but I don't know how to do that.
I have several checkboxes on the same sheet. I already have the checkboxes linked to cells, I just need to know how to tell the vba to find the right place to start.
I am wondering if there is a formula/macro/VBA that will find the last date entered in a column and fill that date in to a cell on a linked sheet.
example:
sheet ONE has columns C through S. (each customer has its own sheet) On this sheet, in column H the date of the last payment made is entered. This date of course changes as payments are made, so the row would change as well.
sheet TWO has columns C through O. (all customers on this one sheet) The rows on this sheet remain the same for each individual customer. I would like the last date entered on sheet ONE column H to be auto copied to sheet TWO column O.
Is there a way to have the last date entered on sheet ONE auto copy to Sheet TWO?
We have Microsoft Excel 2000 and i have locked all worksheets separately. I have enabled Data Validation on a range of cells and enabled a list box. The way the workbook works is you log in with a name, the cell range references the names in each cell to another cell which was your login name. Those that match, after clicking a button will unlock those specified cells.
Then users can use the data validation listbox function to select only their name from the cell that was linked to the log in name box. (the users name that logged in only shows in the list). The problem is, all cells by default are locked in the range with the names in. You cannot delete the values in any other way, unless you unlock the cells. (at least thats what is supposed to happen)
My question is:
Is there any code anybody knows that i can use to make excel copy what excel 2003 does, im sure that the problem is a fault with excel 2000 and microsoft fixed it when they released excel 2003.
I have a DDE linked cell that is constantly updating (cell A1), I want then to have a cell (cell B1) that changes only when cell A1 differs from value currently in B1 by a certain percentage, then check B1 for the same tolerance level against A1, and repeat. I can do this in a number of ways in VB, however when VB code is running causes the DDE link not to update and since the update is constant I cannot use selectionChange and Change VB Worksheet function result in the same error.
