Found the wonderful code here at Ozgrid and would like to change the return to home selection.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("d4:Ad104"), Target) Is Nothing Then
Int_color = Target.Interior.ColorIndex
Select Case Int_color
Case xlNone
'Question, what does "xlNone" do?
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 3
Target.Value = "true"
'Range("A1").Select - would like to change this to move the selection to
'the present row clicked, column c. If I click on G9 the selection returns
'to C9.
Case Else
Target.Interior.ColorIndex = xlNone
Target.Value = "false"
Does anyone know the equivalent of CTRL (Home) to move the cursor to the a1 position in VBA? I have tried Range(cells(1,1),cells(1,1)).select but with no success
I'm trying to design a form, for various staff members to populate. I would like to ease their usage by putting a check mark in which ever cell they left click, also if they click a second time in the same cell it will undo the action.
I have not decided yet which which cells I am using, but is their some simple routine that could be used over & over again and all I would need to do would (hopefully) simply be to change the cell numbers in the routine to apply to that cell or range of cells.
I am looking for assisitance in combining the functionality of two seperate combo box code sets. The first combo box code set allows the combo box to automaticially appear when a user Clicks on a cell that contains a data validation list. The second set of code will have a combo box appear when a user Double-clicks on a cell that contains a data validation list. And the code allows for Named Ranges on a seperate worksheet. I would like to have the functionality of having the combo box to appear when a user Clicks on a cell and not having to Double-click. Also, I would like to have the option to use Named Ranges on a seperate worksheet ("Validation Lists").
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim str As String Dim cboTemp As OLEObject Dim ws As Worksheet Set ws = ActiveSheet On Error Goto errHandler If Target.Count > 1 Then Goto exitHandler Set cboTemp = ws.OLEObjects("TempCombo") On Error Resume Next If cboTemp.Visible = True Then With cboTemp .Top = 10 .Left = 10 .ListFillRange = "" .LinkedCell = "" .Visible = False .Value = "" End With End If................
I have a reference sheet that the only cell that should be accessed is in col A
Rather than protecting the sheet I have made it so that what ever cell is selected it changes to select the cell in A
however if the selection is made using the col, header (ie clicking the C) it falls over.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(ActiveCell, Range("A6:A1000")) Is Nothing Then 'makes sure A isn't already selected x = ActiveCell.End(xlToLeft).Cells.Count ActiveCell.Offset(0, -x).Select Else If Selection.Cells.Count > 1 Then ' fixes the problem of selecting a range that starts with an A ActiveCell.Select End If End If
I am trying to develop a worksheet that will adjust dates based on a selection from a drop down box. I can do basic formulas in excel
I am trying to get the drop down box in "Revisit Type" to include three selections ("Sanitation", "Spark", and "High Five"). Once the dropdown box is created I want to be able to make a selection and enter an initial visit date under "Initial Visit Date" then have the "Earliest Return Visit Date" and the "Latest Return Visit Date" automatically populated based on this information below:
Sanitation or Spark = 10-30 day window High Five = 10-21 day window
I am at a loss . I have attached a sample of the workbook for reference.
Changing the data based on a drop down selection. My drop down list is based on the months I need the data (percentages for each person) to change depending on which month is chosen. So If I select Jan, the percentage fills in for each person. I've attached an example.
Attached is the file i am working with. The table spanning from A:AA5:16 contains hours worked per day of the week with respect to the Project chosen from the drop down menus in A7:16. Cells in the range of F,K,O,R,U,X,AA7:16 total those hours worked on a daily basis. The table below should sum the total hours worked per day according to the Project selected in A19 from the drop down menu, with respect to the Project(s) chosen in cell range A7:16. For example, if i am looking for total hours worked Monday on Project RSB i select "RSB" from the drop down menu in cell A19, i want cell F18:19 to be the sum of cells A7 and A9, with respect to the Project selected from cells A7:16. If I select "FRG" from the drop down in cell A19, i want cell F18:19 to reflect the sum of cells F8 and F10.
What I want to do is randomly select either number 1 to 11 (leftmost column OR the person it represents) but also make the selection based on the percentages in the rightmost column. To illustrate using the above example, Number 11 (Massimiliano Esposito) should have 12 times more chance of being randomly selected than Number 1 (Artur Boruc)...
I have used a formula such as
PHP =INDEX(A$1:A$11,INT((RAND()*11)+1),1)
to randomly select a cell but how would I modify this to include cell weightings? Or is there a better formula?
An additional note I have is that the weighting for each person will always be changing, but will always be a percentage.
I am writing a sports schedule or fixture list. The home teams are in column B and the away teams are in column C.
I want to prevent any team having 3 consecutive home matches. Is there any way a formula can be used to alert me if there are 3 consecutive home matches.
I have been searching Excel Help for a code that will allow me to program the Ctrl + Home function.
Specifically, I have a macro that leaves the selected cell too far down on the spreadsheet. Instead of making the user scroll up or press the Ctrl + Home keys, I want to write a code at the end of my existing macro.
I have used the PMT function but this gives me the total to pay per month. I want to know what the repayments would be and also allow me to add in each one of the periods and extra repayment option. So if in the year I wanted to pay an extra $100 per month, I would put $100 next to each period as in a particular period(s) I might not have to put in there but want to predict what the amount owing on the house is. Is this possible or is this too complicated.
On the computers in my office they have a My Documents folder that is in the directory C:Data. There is also a My Documents folder in the usual windows location C:Documents and Settings etc. Is there a function that will give me the path of the folder that is on the desktop? The macro will be run on computers with the folder in different locations which is why I need to look it up.
I am working on a budget spreadsheet and want to find a formula that will automatically tell me what my take home pay is depending on the yearly salary and see if covers my expenses (linked to another spreadsheet). It will be easier to budget my expenses depending on my salary. My attempts to create IF and = haven't worked.
I created a custom tab withthe UI editor into my personal sheet which is set as startup and I want to have this tab automaticly shown when it is opened.
So when the workbook is opened I would like the show the tab Andre rather than the home tab.
I have an older laptop with XP and 2007 Office / Excel this works, with the new laptop doesn't work?
basically - I open a new spreadsheet - blank - just basic
I used to be able to click the data tab, then click From Access, then enter my URL to an access database on my website and BAM my data would populate in cell A1
with VISTA - I get the same error over and over again, tried a different XP machine, no problem? What gives?
error box states: Microsoft Office Excel cannot access the file [url] there are several possible reasons.
the file name or path does not exist the file is being used workbook has the same name
I'm starting to really hate VISTA after about 3hrs with this new laptop!
So I'm looking for a formula that returns information to one cell. A formula that looks if in 'this' column you see the category code '1' for example, then return to me the money spent for ALL the 1's. It should return $17...
The table above is small just for demo purpose, my table has 8 category codes at the moment and the number of rows can be between 15 and 50 I guess... it's a monthly break down.
I would like to see how much is spent in total for each category...
In addition if possible, can this be done for two separate tables... We are two people, and I have two table recording expenditure.
Look up in both tables, search the two category columns for category '1' for example, then return all the $'s added together... So for Category 1 now it would show $49...
When using 2013 Excel in the Home tab, line 1 is not visible. Using the File tab will show line one, but the Home title bar is unavailable. Toggling back and forth is not efficient. No one in this office has ever seen an Excel program not display a full page under the title bar in the Home tab.
I recently moved to Central America and now am working on models that are either in $ or in the local currency (Q). I do not want to change my default currency value in the regional settings of the control panel because it is more convenient for me to stay in $ but it is a pain everytime I want to set a worksheet in Q and not in $.
So, I am wondering how I can change the default values in the currency drop-down list on the Home toolbar under Numbers in Excel 2010.
Currently, it gives me the change to apply the following currencies:
- $ English (US) - £ English (UK) - € Euro (€ 123) - ¥ Chinese (PRC) - fr. French (Switzerland)
Instead of either the £, ¥ or fr., I would like to have listed the Q, my other frequently used currency.
I know this has been discussed a number of times, but here is my problem I have three charts in my workbook. I want to attach a macro so that when the chart is clicked it returns to Sheet - Home. I have using the following: worksheets("Home").activate. But after I protect each chart and the workbook, and save and exit. When I reload the Workbook it has forgotten the assigned macros and nothing happens.
If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.
I have a database of pricing codes made up of several sheets. One of the sheets has a table of discounts. Each genre of product is assigned a code that determines the discount. My pricing code sheets include the discount codes. I want to use the discount code in the pricing code tables to call on the discount table and populate a cell in the pricing table with the discount multiplier. So...
In Sheet1, Column A has the discount code for whatever product line is in the row. In Sheet14 are all the discount codes and all their respective discounts. I want to take the code and search for it in Sheet14 and then place it in column F for each row of Sheet1. So the code in A1 is "abc". I want to find the row in Sheet14 column A with code "abc" and take the value in column L of that row (the discount multiplier) and place that value in F1. I would have every row of the pricing code tables doing the same thing.
The discount multipliers change often so I want to be able to just drop a new table into Sheet14 with the updated multipliers and have all my other sheets reflect the change without having to manually enter the new multipliers.
I tried very hard to design a leave roster for user to mark their leave application. The criteria are as follows:
1. 4 applications per date 2. a region of worksheet (i.e. "A2 to H20) will be defined for users to "click" on the cells (within the defined region) to mark their application. 3. once a cell is clicked (i.e. marked), it cannot be altered. 4. each click will automatically increase the total by 1.
I got the following code to deal with the situation but user can still click on any cell outside the region (in fact I fail to define the region).
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim m ad = Mid(ActiveCell.Address, 2, 1) m = Range(ad & 24).Value 'here a formula "CountA(A2,A20)" will be place in the cell (24, c).......................
I have attached a portion of an excel file I am using at the moment. What I require is a piece of VBA code which will allow me to copy selected data based on a combo box selection. What needs to happen is this :-
If the selection in the "Index" worksheet combo box related to "Month" in cell G19 is for instance "December", I would like the macro to compare this cell value to the cell values in cells C96, C124, C152, C180, C209, C236, C263 and C290 in the "Tech Services" worksheet and where the values match.......copy the commentary (Range C126:Z147 in December's case) to the range C34:Z55.
I am working on a project where user has to select an item from a listbox, however there is a condition that if user select an item, other items of listbox should be disabled so that he/she can not select any other item.
I want to have a drop down list in a cell so that the value in the cell can be only selected from two columns of data. Additionally once the data from the first column has been selected I want to be able to limit the inputs the user can select from in the second column.
e.g. In cell C115 I want to have the value BDS05.
I want to be able to select the value BDS from a drop down list of values and once that value has been selected I want to be able select 05 from a list of values from 01 to 14 If I select BCS as the first value then I want the second set of values to be limited to 01 to 02 etc.
I have read about combo boxes and list boxes and I'm a bit confused about the best way to achieve this (or even if I can).
