I am having some difficulty working out how to activate the SelectionChange command if the user changes a cell in a particular column. Based on the fact that the column number may change the constant in this column will be that on row 7 the name will be "Fund Size".
Therefore my question is how do I get VBA to run my code if a user changes a cell in the "Fund Size" column...and underneath the "Fund Size" header (i.e. row 8 or greater).
Is there a way to switch back and forth between workbooks without having to use the Activate command? I have everything planned out but this aspect is still a source of confusion. The Project: I need to have qty purchased and price in a table from Worksheet B entered into the matching customer index location in Worksheet A. Worksheet B contains customer number, sku, qty purchased and price. Worksheet A contains customer numbers in row 3 and sku's in column A. Minor data manipulation will need to be done on Worksheet B before before accessing it which I plan to do after opening it in this macro.
I want macro2 to activate whenever there is a change in Range(C25:C5000). I want macro2 to activate only once even if there are 10 changes to the cells within this range. I tried the code below but it does not work.
Private Sub Worksheet_Calculate() If ActiveCell.Row > 25 And ActiveCell.Row < 5000 And ActiveCell.Column = 3 Then macro2 End If End Sub
I have workbook with two sheets "Path A" and "Path B". Whenever I activate worksheet "Path B", I want to activate the cell in "Path B " which was the active cell in sheet "path A". For example, if cell R1 was active in path A when Path B was activated, R1 should be made the activecell of Path B. Tried the follwing code... not working.
Private Sub Worksheet_Activate() Dim r As Integer, c As Integer r = Worksheets("Path A").ActiveCell.Row c = Worksheets("Path A").ActiveCell.Column Worksheets("PathB").Cells(r, c).Activate End Sub
In tab Sheet1 there is Commandbuttons named "Sheet2" & "Sheet3". If i press "Sheet2" then i will be directed to sheet2 tab, same as "Sheet3". In tab Sheet2 & Sheet3 there is commandbutton "Sheet1", if i press the button then i will be directed to default tab which is sheet1.
I want to filter a list. Within the filter I want to copy a selection of cells and hard copy the formula in those cells (copy paste special values). It is possible to selected the cell and copy them but one cannot paste on a filtered range. The error message I get is "The command cannot be used on multiple selection".
I have a fairly straightforward UserForm with 4 listboxes and 4 option textboxes (the textboxes do not need to be a part of the validation I'm needing). I then have a command button that I want to be enabled ONLY if ANY of the 4 Listboxes have a selection. I've tried the "Change" event code below and it works upon the first selection of any listbox item.
However, if the user de-selects all selections in the listboxes (i.e. they are all unchecked), the command button remains enabled. I need the validation logic to enable/disable the button to persist as long as the user has the form open and if there is not a selection in ANY of the listboxes. The reason being is that if they click the command button it will update the cells in the worksheet, which if empty may overwrite previous work completed with blank cells.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) col = ActiveCell.Column Range("output") = ActiveCell.Offset(0, -(col - 4)).Value End Sub
In case it's not obvious, the macro places the value in the active row and 4th column of the worksheet into the range "output". The problem is, the worksheet is large and somewhat slow to recalculate. This macro forces a recalc on any selection change, but i only need it to run when the row selection changes, not the column. I'm sure there's a straightforward way to reprogram this.
I have a main worksheet that has two combo boxes. The first combo box has a list of machines. The second combo box brings up a list of models for the type of machine selected. I would like to insert a command button, that when pressed will open a different worksheet with the specs of the model selected in the second combo box. I am having trouble finding a starting point for the code to have the command button perform this.
I am trying to Change Multiple cell values using the command button. So once clicked it should change the value in the cells with the value its getting from the Inputbox. This function also changes the cells in multiple worksheets. For some reason its not working and I can't figure the problem out.
Private Sub DateChngButton1_Click() Dim strDate As Date Sheets("Summary").Select strDate = InputBox("Input the date eg. april 2004", Title:="Date Change Prompt") Range("H4").Select ActiveCell.FormulaR1C1 = strDate Range("I18").Select ActiveCell.FormulaR1C1 = strDate Range("I32").Select ActiveCell.FormulaR1C1 = strDate Range("I46").Select ActiveCell.FormulaR1C1 = strDate Sheets("Cover").Select 'strDate = InputBox("Input the date eg. april 2004", Title:="Date Change Prompt") Range("C22").Select ActiveCell.FormulaR1C1 = strDate End Sub
I'm trying to write a code to make a number of buttons visible depending on a cell value
I have 10 command buttons all are invisible and I want to show only the first x x is the value of cell "A1" in "Sheet1" (will be from 1 to 10) Command buttons names are default names (CommandButton1, CommandButton2, ... , CommandButton10)
I am trying to create a program to automate the gauging figures on oil barges, currently everything is done by hand and takes approximately 20 minutes to complete. I have the charts entered for the tanks already and have the code set so that when you click on a tank "gauge" it will enter the "volume" which corresponds to that gauge on a totals sheet. Here is where my problem is coming in
The barges are gauged at four points "before loading" "after loading" "before discharge" "after discharge"
I want to set a worksheet as the default page with four command buttons that let the user select which operation he wants to perform. before load figures. after load figures and depending on which operation they select have it enter the volumes in the appropriate cells on the totals sheet. So if someone selects "loadopen" command button I want the following code to run on my worksheets............................
I have this code for 10 Command Buttons to change the font colour. This is part of a much larger piece of code, but to simplify it, I am just using this Sub ForeColour() Sheet1.Cat1.ForeColor = &HC0& Sheet1.Cat2.ForeColor = &HFF0000 Sheet1.Cat3.ForeColor = &HFF0000 Sheet1.Cat4.ForeColor = &HFF0000 Sheet1.Cat5.ForeColor = &HFF0000 Sheet1.Cat6.ForeColor = &HFF0000 Sheet1.Cat7.ForeColor = &HFF0000 Sheet1.Cat8.ForeColor = &HFF0000 Sheet1.Cat9.ForeColor = &HFF0000 Sheet1.Cat10.ForeColor = &HFF0000 End Sub
In Excel 2003, this code works instantaneously, but I am trying to run the same code in Excel 2000 and it takes 10 seconds. Is there any way that I can speed this up in Excel 2000?
The original file was created in Excel 2003, but I have created a new file in Excel 2000 with the 10 buttons on it and the code and it still takes 10 seconds.
I am looking for a way to alter the appearance of a button when a second one is clicked. I have two buttons, "Yes" and "No", and when one is clicked I would like to 'grey' the font in the other without disabling it, and vice versa. I have tried recording a macro as I go into the button's properties and change the foreground font, but it does not record the font change, only the selection of the button object.
My user wants to change it so that, when the word "Triang" is selected from a dropdown box on each line the formatting goes back to none......or ClearFormats.
Can this be done with a Worksheet_selectionChange or similar. I can't even get my head around this at the moment. What the user is saying is, if the Formats haven't been cleared on certain lines, the user of the sheet hasn't addressed the needs for that particular line.
For r = lrow To 15 Step -1 If Range("M" & r).Value "" = True Then Range("N" & r).Value = "Triang" Range("N" & r).Interior.Color = vbRed Range("V" & r).Value = "Triang" Range("V" & r).Interior.Color = vbRed End If Next r
I'm trying to write some code where if the user hits the Enter key on a particular cell an input box pops up. At the moment I've got some code that activates the input box whenever the user selects or scrolls over the cell. The problem is that the cell is a protected cell (as it has a formula in it) so double-clicking isn't an option.....
The following macro will put a checkmark in the range of A2:B100. I would like to use the current macro along with two or more other Worksheet_SelectionChange. One of the SelectionChange to put "Williamson" in the range of L2:L100 and another SelectionChange to put "Michaelson" in the range of M2:M100? Is it possible to use more than one Worksheet_SelectionChange
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "A2:B100" '
I am getting US formatted dates with this procedure, I require UK dates, but I don't understand why its giving me US format
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If UCase(Sh.Name) "ALL BILLS" Then Exit Sub With Target If .Row > 5 And .Column = 19 Then If Cells(.Row, "B") = "" Then Else Application.EnableEvents = False Cells(.Row, "S") = "CLOSED" Cells(.Row, "E") = Format(Date, "DD/MM/YYYY") Application.EnableEvents = True Range("E:E").EntireColumn.AutoFit Range("S:S").EntireColumn.AutoFit End If End If End With End Sub
i want the user to be able to change the cell color on the click of a command button. the worksheet is protected. when the user click the command button the active cell changes to red and offsets by 1. then the work sheet is locked again.
The two problems I am having is 1. I want the range to begin from row 10, column k to column FD. all cells down
the second problem is the current code allows me to edit locked cell columns A to J ...