I'm just finishing a project that is a collection of three calculators, each of which returns data based on a series of user-supplied variables. All the mechanics are working [thanks in part to replies to previous posts], but there is one more thing I'd like to add:
Does anyone have a way to clear the contents of another cell [actually several] cells via another cell? As the most complex calculator requires eight variables to be entered, I'd like to provide a quick way [a button?] to "clear" or "reset" the calculator - basically wipe all unlocked cells. I should note that all of the cells in question already have Data Validation of one type or another.
I have 4 combo boxes with selectable options and several cells for inputting data into. I'm looking to create a reset button that returns combo box values back to the first one in the list and clears all cells at the same time. I've figured out the cell reset as follows;
I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5 E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
I have a userform with several combo boxes on it. I also have three command buttons, 'OK', 'Reset' and 'Cancel'. I want to code the 'Reset' command button to reset the values of the combo boxes to empty or "". I have attempted to do this simply by using the following code.
Private Sub cmdReset_Click() cmbWaterTemp.Value = "" cmbTypeFastIce.Value = "" cmbIceDrift.Value = "" cmbTrendBehavior.Value = "" End Sub
When I click on the 'Reset' command button I get the following error.
"Run-time error '94': Invalid use of Null"
When I use the debug tool the error pointed to is a different sub.
Private Sub cmbWaterTemp_Change() txtTemp1Encoded.Text = Left(cmbWaterTemp.value, 1) txtTemp2Encoded.Text = Right(cmbWaterTemp.Value, 1) End Sub
I am not exactly sure why the error points to this sub. I know there must be a simple way to accomplish this. I am including a workbook that is similar though not exactly the same due to file constraints. What I want to happen is when the 'Reset' button is clicked is to clear all values for the combo boxes and the text boxes. I have successfully gotten the textbox values to clear although not the combo boxes.
I am working on some code that I would like the user to be able to re-run should the underlying data in the adjacent tabs change. My existing code is shown below
Code: Option Explicit Dim wb As Workbook Dim ws As Worksheet
I'd like to be able to remove all data on the ALL tab so that when the UsedRange.Rows.Count is run again on that page it will only see the header row. In the past I've deleted or cleared data from the sheet and the Rows.Count still counts the rows which have no content in them.
A coworker has come to me asking for a way to indicate on a sheet whether or not any autofilters are applied, and a button to reset all the filters. The second part is easy, and I've got a CommandButton with the following code to do it:
I've included the code I'm using to calculate the net weight depending on which option button is selected. I use a flag ("VLAG") to know whether it's the first time the button is pressed. All variables are DIMmed at initialization (different sub).
By using other flags, I can see the code runs perfectly, but the tare weight ("TARRA") is not incremented each time the button is pressed. The formula I'm using to increment is
I want to add a secondary function to these buttons; this is a necessary function for every order, so I want to make it necessary to click one of the buttons for every order. One way to do this would be to leave the button unchecked.
How could the button be set to leave itself unchecked after being clicked on?
I have a spreadsheet with 4 combo boxes and 2 cells that I need reset to a specific value when a button is clicked (the top value for the combo boxes, the function [today()] for one cell, and a specific date for the other). I figured I would put the function and the specific date in seperate hidden cells and the button would link to them, but whatever way gets the job done will work. I know I'll need to code this in VB, but as well as I know excel i've never played with basic before.
I'm having a problem creating a Reset All command button that will remove user entered unlocked cell values from an entire workbook save for one specific worksheet. So for example, I have five tabs labeled as "DTF", "Week 1", "Week 2", "Week 3" and "Week 4". I want one single command button (placed in the "DTF" worksheet) to remove all of the unlocked cell values from "Week 1" to "Week 4".
I have never written or used a macro before and I have a simple macro task to complete:
I have Column L (L4:L10) of seven scroll bars that move according to number changes in column F (F4:F10). I want to create a form control command button that resets the changes on the scroll bars according to static column of numbers in Column E (E4:E10).
I don't know if it matters, but I'll add that I plan to add a second button that does the same thing with a different column of data. I assume I'll simply replicate whatever procedure I use in the first button - is this okay?
I want to create a command button that will clear whatever is written in a text box. I guess for this to work it needs to select all, then clear. The command button and the text box have been created using the control toolbox.
In ThisWorkbook Userform2 is triggered upon a double click
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
UserForm2.Show End Sub
Userform2 has Frame1 (Option Buttons 1-6) and Frame2 (Option Buttons 7-12)
Private Sub OptionButton1_Click()
Call ProjectBuildings Range("A1").Select End Sub
Private Sub OptionButton10_Click()
Call BondIndustrial Range("A1").Select
The user can click away and things work great. The macros that are triggered search a number of Workbooks and individual Worksheets looking to select either of two (hence the two Frames) specific items.
If the user clicks any Option Button in Frame 1 (works) then any Option Button in Frame 2 (works) then goes back to the same Option Button in Frame 1 (nothing) I have a problem.
How do I clear the first Option Button in Frame 1 after the user selects another Option Button in Frame 2?
I have created a command button, right clicked the button and selected 'View Code'. In the code window I've entered the following code:
Private Sub CommandButton1_Click() IB_FB_Hedge_3 Outcome.range(W10:X11).ClearContents End Sub
As soon as I enter the code a message pops up:
Expected: list separator or )"
Is my syntax wrong? Is the sheet name, 'IB_FB_Hedge_3 Outcome' not allowed?
When I created this code a few minutes before I was not having the 'Compile error' problem, but the Command Button was not working either. When I clicked the command button it would just show the circles at the corners, suggesting that it is still in editing mode and not properly activated as a command button.
I keep encountering an error while creating multiple sheets within the same workbook. This is a known error by Microsoft as documented here. In order to avoid using multiple workbooks i need to clear excels cache each time I run the macro as i have limited users sheet creation to 30 sheets which should not trigger the error. Does any one know a code to clear excels cache or memory?
I have used cutcopymode = false but that does not do the trick.
I have seen this before in spreadsheets so I atleast know it is possible.
I have a worksheet that has 6 sheets in it. The final sheet is a formula page that only shows results if sheets 2-5 have data. Sheet 1 holds all the master data. Sheets 2-5 show subsets based on sheet 1. I manually atm filter sheet 1 for certain info and then copy paste into 2-5.
Seeing as I do this weekly. How would I go about adding in a 7th tab or even a macro that will delete all data from row3-end in tabs 2-5, then copy the appropriate data from tab 1 based on a set criteria?
I could write this in a macro just wondering if there is an easy built in way?
Function FindNth(Table As Range, Val1 As Variant,Val1Occrnce As Integer, _ Val2 As Variant,Val2Col As Integer, ResultCol As Integer) Dim i As Integer Dim iCount As Integer Dim rCol As Range For i = 1 To Table.Rows.Count If Table.Cells(i, 1) = Val1 And _ Table.Cells(i, Val2Col) = Val2 Then iCount = iCount + 1 End If If iCount = Val1Occrnce Then FindNth = Table.Cells(i, ResultCol) Exit For End If Next i End Function
To use this function, push Alt+F11 and go to Insert>Module. Paste in the code, push Alt+Q and save. Use the function as shown in graphic example. You can access it under "User Defined" in the Paste Function (Shift+F3).
Type Amount Sex Cost Purchased Cat 1 Male $ 5.00 22/05/01 Cat 2 Male $ 5.00 15/00/00 Dog 3 Male $ 20.00 25/04/00 Rat 5 Female $ 1.00 15/08/01..................
I am trying to create a command button in Microsoft Excel (Office 2000) that enables me to reset the check box status for a worksheet from checked to unchecked. The worksheet contains approximately 50 check boxes and is meant to be reused. Is this possible? If so, how can I program it to do so?
1) We need the Input screen to auto clear the fields once the " Save " button has been pressed and data transfered to the appropriate tracking sheet
2) We want to ensure that if any of the Red cells are empty the " Save " button will give an error ( one complication If the "Transfer Type" highlighted in Blue has an entry " either STD/ISD/ESP " then " Call Purpose " needs to be mandatory
3) somehow the information on the "Comment " field is not getting captured
4) If possible we want a warning indicator if two rows in a tracking sheet ( example London ) are the same based on Coloums B to I ( except C )
5) Is there any possibility to give a pop calender in the " scheduled date " section of Input screen (Row 33)
The Save button transfers data from my userform to a worksheet via variables.
It then proceeds to use the variables to generate further data, dependant on a combination of the variables, as this process creates a row of data on the first available empty row, as this script is being used in a loop, ie add another record yes or no.
I need to incorporate a method whereby the user at any point can click on cell S(x) where (x) corresponds to the row clicked, which will then run a macro which will allow me to change a value from OPEN to CLOSED and also add a userentered date into a field called DATECLOSED, it is imperative that this process only work on the row in question.
creating a button (using VBA code) in a excel worksheet that can initiate a range of output data in a col (say (C1:C100)) by activating the therein formula of each cell (same as what F2 does) and then entering the cell.
Actually I have to do manually each time for each output cell after opening the worksheet that I don't want.
Sub AddFormsButton() Dim sShape As Shape With Range("J1") Set sShape = Sheets("Exposure Country").Shapes.AddFormControl _ (Type:=xlButtonControl, Left:=.Left, Top:=.Top, Width:=96, Height:=20) End With
Which is called within another macro and inserts a button onto sheet Exposure Country, after which the macro it was called from then stops (i.e. End Sub)
What I would like is that when the user clicks on this button it selects another sheet but I'm not sure how to achieve this.
I have this request excel sheet which needs a button to do multi functions:
First the sheet is placed in remote server and had two working sheets (say sheet 1 and sheet two), in sheet one there are fields must be filled by the user and a sequence number must be issued for each request.
1- when the button clicked the request serial number must be added by one (counter). 2- log the new serial number, user name and datetime in sheet 2. 3-save the sheet on local pc 4- disable the button after click so that the user could not click the button twice for one request.
Is it possible to create a button in or out of a cell and when that is clicked it will generate a new sheet in the same workbook.
Now this is throwing the line to you clever people... Having then created a new sheet can it be that in say A:1 it will display the date the sheet was created AND make a popup box appear to allow the user to enter a name for the new sheet?