|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another
Hi,
I'm new to VBA and so was wondering if someone knew where I can find information on how to do something I'm working on. Basically, I have a list of tasks that need to be completed for quality checks of customer statements that are being written. I've added checkboxes for each task to keep track of any issues performing the tasks, and would like a way that if they are checked off as the list is worked through (tasks screenshot), then a log of where the issue was can be made on a second sheet (log screenshot) linked to each customer. I've figured out how to make a droplist at the top of the task list generated from the list of customers on the log sheet, and would like a way to link the checkboxes to each customer. Can this be done? Thanks! |
#2
|
|||
|
|||
Quote:
Chances of assistance is greater attaching a workbook rather than pictures of sheet(s) in the workbook. |
#3
|
|||
|
|||
I will try and record macros for each one
Quote:
I've attached the file now. So my aim is a customer would be selected from the dropdown list, and then whoever is using this would work through the checklist of tasks ticking a checkbox if there was an issue. The second sheet would serve as a log where each row is a customer, and the columns are the tasks, so the number of issues and where they occurred can be recorded per customer |
#4
|
|||
|
|||
I tried to record a macro for each checkbox but it reverts to the original selection
I tried recording a macro and getting it to copy the contents of a cell (the dropdown of the customer name) and then do a ctrl + F to find the row of that customer to fill in the cell. However, when I run the macro it just reverts back to the customer which I recorded the macro on. When I view the code to edit it, it specifies the customer. How should I write it so it copies the cell contents and doesn't revert to the original dropdown selection?
Here is the code if it helps: Code:
Sub AM_Missing_tick() ' ' AM_Missing_tick Macro ' ' Range("A1:E1").Select ActiveCell.FormulaR1C1 = "2gether NHS Foundation Trust" Range("G1").Select Sheets("Recorded Errors").Select Cells.Find(What:="2gether NHS Foundation Trust", After:=ActiveCell, LookIn _ :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate Range("B4").Select ActiveCell.FormulaR1C1 = "x" Sheets("Quality Check").Select End Sub |
#5
|
|||
|
|||
See if this helps do what you're after.
Added macro to module 1 to apply .OnAction macro to be run by the column E checkboxes when clicked. Added columns to "Recorded Errors" in order to retrieve status when changing customers in drop down in A1. Used Worksheet_Change event on module of "Quality Check" sheet for retrieval. Module 2 has the macro that runs each time one of the column E checkboxes is clicked. |
#6
|
|||
|
|||
OK wow that it perfect. I managed to play about and get it working but I only had it tied to when the checkbox was clicked, not actually undoing it if it was unchecked. Yours is soo much better!!
Thank you so much!! |
Tags |
checkbox, excel 2016, vba code |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Linking Specific text fields in PP to specific cells in an Excel table | GWRW1964 | PowerPoint | 0 | 02-26-2018 07:37 AM |
Export Word Drop-Down Content Control to Excel Specific Sheet | nolanthomas32 | Word VBA | 4 | 09-19-2017 06:25 AM |
extract specific pivot table data to a new sheet | theexpat | Excel Programming | 0 | 02-18-2016 10:08 AM |
Excel copy two numbers between specific column and paste in next sheet | visha_1984 | Excel | 1 | 12-26-2014 07:59 PM |
Browse to a specific sheet in an excel workbook | Sinister | Excel | 4 | 04-08-2014 05:53 PM |