Unchecking Checkbox In Separate Worksheet Using VBA

Jan 4, 2013

I have a workbook containing many different sheets. What I want to accomplish is when I click on a check box on one sheet, I want the check box in a different worksheet to be unchecked. I have form control checkboxes in my workbook, so I used xlOff as the value.

Here's what I have in my VBA code.

Worksheets("February").CheckBoxes("CheckBox13").Value = xlOff

It is giving me the error "Unable to get the CheckBoxes property of the Worksheet class"

View 4 Replies


ADVERTISEMENT

Unchecking A Checkbox Hides A Sheet

Oct 3, 2007

Code that will make it so that when a checkbox is UNCHECKED, the sheet that it originally opened re-hides itself? In other words, I have a mcor that works great that allows for a checkbox when checked to unhide a worksheet and take the user to that sheet. Now I need it si that when it is UNCHECKED, the sheet goes back to it's hidden state. This is what I currently have that UNHIDES it. Obviously somehow I need it to REHIDE it after being unchecked:

How would the corrected code look AFTER being added to this one:

Sub CheckBox615_Click()
Sheets("FedEx Freight Opp Form").Visible = True
Sheets("FedEx Freight Opp Form").Select
Range("B16").Select
End Sub

View 9 Replies View Related

Unchecking All Boxes With A Macro

Sep 27, 2008

Is there a macro that will uncheck all the check boxes in a col?

I have check boxes in cells j1:20

View 9 Replies View Related

Create A Separate Worksheet That Has The Data In And Reference Cells On Budget Worksheet?

Feb 7, 2014

I need to reference the projected and actual expenses from the total on worksheet 'expenses' So i did so, however, if I change the drop down on the expenses worksheet to only display housing data, then the projected and actual expenses on my budget worksheet changes as well to the new data portrayed on the expenses worksheet.

I need to reference the cell, without it changing when I change the category display, but I need the cell to change accordingly if I enter new data in the overall tablet on the expenses worksheet.

Or do I need to create a seperate worksheet that has the data in and reference my cells on my budget worksheet to that new worksheet?

View 1 Replies View Related

Removed It By Unchecking ViewTool BarTask Pan

Feb 21, 2007

I have MS office 2003.

When I open new excel sheet, It opens with " Getting started " office online help page on right side of sheet.

I removed it by unchecking ViewTool barTask pan. but next time I open new sheet it is still there.

View 9 Replies View Related

Checking / Unchecking Existing Values In A ListBox

Nov 5, 2012

I have a Multi Select ListBox with values from a range of cells.

I wish to automate the selection of one or more existing value(s) via VBA code so when User views the List, The check mark is visible. This will alert them to Active values.

i.e ListBox:

North
South
East
West.

If the active region is West, The check box beside West is Active, i.e contains an "X"

View 4 Replies View Related

Macro For Unchecking Protection Properties While Protected

Dec 4, 2006

While my excel file is protected, I want to have a functionality that allows user to modify on certain areas (of the file), such as below:

1. After the user select number of years at the drop-down list (Main sheet), rows and columns at other related sheets will shift and hide certain values which corresponded from the drop-down list (e.g. if user selects 3 years, then at other sheets, rows from year 1 till 3 will be displayed, and rows for year 4 and year 5 will be hidden).

2. Users can add rows for entering new data at a new line and delete rows for deleting data at the correspond line.

This PC is using the Excel version 2000. At Tools > Protection > Protect Sheet, the available options are 'Content's, 'Objects' and 'Scenarios'. In order to allow users modifying rows/columns (while the file is protected), options such as 'Format Rows' and 'Format Columns' must be checked. But these options are only available in the later versions I believed.

After I have done macro recording for the protection properties at Excel version 2003 (my sister's PC), this is what I got for the .....

View 9 Replies View Related

Get State Of Checkbox On Worksheet

Jan 14, 2009

I need to get the state (xlon or xloff) of check boxes on a worksheet. i have tried the following but unable to get it to work.

View 3 Replies View Related

Check Value Of Checkbox On Worksheet

Dec 14, 2007

I have a problem to check the value of a checkbox when I write the code in Module1. I have several worksheets, and in each worksheet there is a CheckBox1. I want the macro (in Module1) to perform a given code when the CheckBox1.value = true and visa versa.

The code I'm using is as follows:

Option Explicit

Global ws As Worksheet

Application. ScreenUpdating = False

For Each ws In Worksheets

If CheckBox1.Value = True Then
'Do code1
Else
'Do code2
End If
Next ws

Application.ScreenUpdating = True

End Sub

When the Macro is run, an error msg is showing "RunTime error '91'.

Does anyone know how to make this macro function? I've searched the forum, but couldn't make any of the codes suggested to work.

View 3 Replies View Related

Transfer Checkbox Caption To Different Worksheet?

Jul 1, 2014

I have 71 checkboxes that I want to transfer the Checkbox Caption if true. If the checkbox is false I want to transfer a value of "0". The captions are being transferred to a different worksheet. Below are a few entries if I was to enter them all separately, but I assume there is a way to do all at once?

View 4 Replies View Related

Worksheet CheckBox (Hide Unhide)

May 22, 2009

I am having difficulties with this one. I have a button on my worksheet (sheet1) that runs a macro.

View 4 Replies View Related

VBA - Disable Form Checkbox In Worksheet

Jan 18, 2013

I want to have a Checkbox (Form) that is in my Worksheet to disable/gray-out once it is selected. I don't want people to be able to uncheck it again. I want them to be forced to click another Checkbox to enable it again.

Example:

Two Checkboxes:

Check Box 1 = "Apply"
Check Box 2 = "Delete"

Once "Apply" is checked, gray it out. This will force a user to click "Delete" in order for the "Apply" button to be enabled again. I do not want a user to click "Apply" once selected as their way of 'deleting' the information.

View 1 Replies View Related

Print Worksheet With Checked CheckBox Control

Sep 22, 2006

I am creating a main worksheet (Legend) in which information will be entered and then populate to other areas (worksheets) within the workbook. I would like to create a print macro - that will run and print all the worksheets that we have selected on the "legend" page. These worksheets would have been selected by ticking a check box.

My question is how to associate a check box to a worksheet? For example - the second worksheet is named " Schedule A" 3rd "Schedule B" and so on.

- The check boxes will be name schedule A, Schedule B and so on.
- I would like to associate (link) the checkbox Schedule A with the Worksheet of the same name. Then when this checkbox is selected, and the print macro runs it will print the worksheets that are selected.

View 5 Replies View Related

How To Create Userform Checkbox Captions On Worksheet Range

Feb 6, 2013

I have a userform with 17 checkboxes that their captions should correspond with column A range ("A2:A18) on a worksheet.

Is there a way in userform_initialize, or activate, to create the captions from that range?

I could type the code 17 times like;

Code:

me.checkbox1.caption = wb.worksheets("area").range("a2").value

I was curious to know if it could be looped? Textboxes on the same userform.

View 6 Replies View Related

Find TextBox Text In Worksheet If CheckBox Is True / Checked

Feb 24, 2008

I have a userform where the textbox already pulls data from a worksheet. I have a checkbox next to the textbox, If the checkbox is true it finds the textbox value on a worksheet and using Offset it inserts "yes" in the next cell.

View 6 Replies View Related

Separate Into Their Own Worksheet

Jul 18, 2002

I have a spreadsheet with 10 or more items that I would like to separate into their own worksheet. I'm thinking about a macro but I'm not sure about creating new worksheets in a macro.

View 9 Replies View Related

To Return The Corresponding Value In A Separate Worksheet

Sep 10, 2007

I am trying to get the lookup function to return the corresponding value in a separate worksheet where it occurs more than once in the lookup range.

At the moment it is only finding the first match. i.e. in

=VLOOKUP(A2,'Appointment Tracker'!A2:B9999,2,0)

View 9 Replies View Related

Getpivotdata From A Separate Worksheet

Feb 23, 2010

i am working on a project where i need to get data from a pivot table that resides on a separate worksheet named "Pivot_by_Group".

I have a worksheet "Budget vs. Actual" and I need to pull data from the pivot table to populate the Actual values.

How can i do that and one more thing, hopw can i suppres errors (#NAME, #REF, etc), i've used the ISERROR function with no luck.

View 9 Replies View Related

Filtering To A Separate Worksheet

Jul 25, 2006

I have a spreadsheet which is a list of dates and amounts.

I want to have a filter on a second worksheet wherein I can type the date and it will filter all amounts for that date.

I've tried the Advanced Filter but I can't get it to auto filter when I change the date. Also it needs setting up every time I do an advanced filter.

View 9 Replies View Related

Checkbox : Married, Widows, Single Checkbox Is Selected

Feb 25, 2009

married, widows, single checkbox is selected

macro solution
good work.

View 5 Replies View Related

Separate And Save Active Worksheet?

Feb 26, 2014

Using VBA, I am trying (without success) to copy the active worksheet of my workbook and save it in the current folder using a filename shown in cell A1. I only need to save values and formats. Any existing code (auto fit) contained withing the sheet would no longer be required. I get a VB project message relating to macros. I would anticipate saving as xlsx would deal with this but again, am at a loss.

View 4 Replies View Related

Open Link From Separate Worksheet

Nov 23, 2008

I have 2 worksheets in my workbook.

One is named "Link Menu" . . . The other is "Setup"

I have an object on "Link Menu" that can be clicked to run a macro.

On the Setup sheet, I have a workable link that opens my email. Whatever link that the user would type into cell D6 on the Setup Sheet would be opened.

I want to beable to click my object on "Link Menu" and have the macro open the link from cell D6 on the Setup sheet.

View 6 Replies View Related

Find Data In Separate Worksheet

Jun 26, 2009

I would like to put together a macro that would compare a list of part numbers against a database, and recommend alternative vendors for the part. The actual VBA I think I can handle, my problem is with the formula, array or whatever is needed to return the results I am looking for.

I have attached a sample workbook to this message that contains two worksheets. The "Sample Database" is an example of the format of the database (in Excel format) that I will be looking against. This data may be a hundred columns wide, and thousands of rows long.

The "Vendor Reference" worksheet shows an example of the data that would be returned if the "perfect" formula were entered into the cells. (I'll explain what I mean by "perfect" in a moment.) It also has an area designated for testing formulas to see what is returned. It's blank, because none of my tests returned anything but errors.

You will notice the column headings on the "Sample Database" worksheet. Columns A and B will always contain the part number and product description. The remaining columns will contain vendor specific data, with perhaps 25 different vendors, and 3 columns per vendor. These columns would be "PV", (for Primary Vendor), the vendor number, (010299, for example), and the Vend Part#.

If there is an asterisk, "*", under the "PV" column for that vendor, that means they are the "Primary" vendor. The cost from that vendor is listed under each vendor number. There may be instances where 2 different vendors are listed as primary. If that's the case, the formula can simply return the FIRST one found.

Here's what the "perfect" formula would return: .....

View 6 Replies View Related

Adding Data To A Separate Worksheet

Mar 14, 2007

I have a userform which when you enter data and hit submit, inserts data into a seperate sheet in the same file. Code below

Private Sub CommandButton1_Click()
Sheets("Purchase Order").Select

UserForm1.tbJobNo.Value = _
Sheets("Purchase Order"). Range("I4")

UserForm1.tbJobTitle.Value = _
Sheets("Purchase Order").Range("K4")

UserForm1.tbOrderNo.Value = _
Sheets("Purchase Order").Range("I49")

UserForm1.cbdirector.Value = _
Sheets("Purchase Order").Range("K43")

Instead of placing the data into another worksheet i would like to put the data into a closed xls file named Purchase Order Database.xls.

How can adjust the code for it to do this?

View 5 Replies View Related

Stop Checkbox Sub From Jumping To Other Checkbox Subs

Apr 16, 2014

I have 3 checkboxes; when one is checked, a set/range of rows should be visible. Only 1 checkbox should be checked at a time.

If checkbox 18 is already checked, and checkbox 20 is then checked, I want the first checkbox unchecked and the rows for checkbox hidden.

I'm using the following code. It works great as long as I check and uncheck the same box before attempting to check another box. But if Checkbox18 is already checked with its rows showing, and I then check checkbox20, the checkbox20 sub runs and as I step through, it jumps to sub checkbox18.

How can I stop my subs from jumping from one to another?

Code:
Private Sub CheckBox18_Click()
If CheckBox18.Value = True Then
Worksheets("TRF").Rows("36:41").Hidden = False
Worksheets("TRF").Rows("42:64").Hidden = True
Worksheets("TRF").Rows("65:76").Hidden = True
CheckBox19.Value = False

[Code] .........

View 9 Replies View Related

Display Values In Separate Worksheet Only If That Value Is Greater Than 0

Feb 12, 2014

I am trying to build a report based on data in a separate worksheet. The report must only show the name of the value and qty associated with it only if the qty is greater than 0.

As an example, the values can be colors.

Column A = Names of Colors: Red, Blue, Green, Yellow, Black
Column B = Qty of each Color: 5,4,0,1,0

The report is held on a separate spreadsheet. I would like to only show the values that have qty's greater than 0, not any values that have a value of 0.

I've attached a sample workbook just in case.

View 7 Replies View Related

Get Master Worksheet To Separate Data Into Various Worksheets?

Jun 17, 2014

i have a worksheet that has information filled in columns A-N and rows 3-7262 (not including title and labels)

i need a macros that will separate this data into multiple worksheets based on the word in Column A; these words could be HDA, CCR/NHT, GRU/ATU (essentially dividing this data up by area name, there will be 14 worksheets)

i would also like to be able to update this master worksheet and have it be reflected in the multiple worksheets that are divided by their areas.

View 5 Replies View Related

Compare Data In Separate Workbook Or Worksheet

Jan 30, 2010

Im working with a huge amount of data in BOOK1.xls and once or twice a week I received an update of the list.

1. I need to compare and find out from the new updated worksheet if there is a new revision, and if there are new documents added to the list. I have tried Vlookup..but it didnt work for me. Ex. Doc1 has revision 1, 2 in my BOOK1 that I am working on. Note that there is a repetition of the DocName, Doc Number except for the revision.

View 3 Replies View Related

Send Userform Entries To Separate Worksheet?

Jul 1, 2013

I need the information that is entered in a userform to be sent to a separate worksheet. Each time a user fills out the form, I would like the information to be documented on the next completely open row (ex. first form goes in row 2 (column headings are in row 1), second form goes in row 3, etc.). I don't know a ton when it comes to VBA, but I believe I have the code figured out to search for the next open row. Column A will always have data in it.:

Code:
dim currentrow as range
set currentrow=Range("A1").end(xldown).offset(1,0).entirerow)

What I can't figure out is how to insert the information to the correct columns. Each column has a header to it, and I would like the data from the userform to be put in the relevant column each time it is filled out.

For example, if column A's header was "First Name" and columm B's header was "Last Name", each time the form is filled out, I would like the first and last name (which was entered in the form) to be inserted into those columns (with each form entry on a separate row).

I think there might be a way to use the intersect method to find the row/column combination I'm looking for, but I'm not exactly sure how to do it. Using the first name example from above, it would basically look for the intersection of "currentrow" and column A, and insert the value that is entered into the userform.

View 2 Replies View Related

Add Dynamic Hyperlink To Separate Worksheet In Same Workbook

Oct 24, 2013

I have read a number of posts and other sites and can create the hyperlink in the desired cell but get an error message to the effect, "Cannot open the workbook."

The current macro follows. I believe its description is made in the comment statements below the date-author comment.

Sub DecomposeCTQ()
'Decompose CTQ Macro
'14Oct13 Charles T. Carroll'
'This macro copies the template to a new worksheet and takes data from the'
' active cell CTQ and makes it the parent CTQ on the new worksheet. Then'
' it renames the new worksheet with the active cell CTQ ID.'
If ActiveCell.Column 2 Then
MsgBox "You must be in the CTQ ID Column to run this program"
GoTo Leave

[code]......

View 4 Replies View Related







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