Microsoft Office Forums Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-06-2019, 05:39 AM
jaffa20 jaffa20 is offline Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another Windows 10 Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another Office 2019
Novice
Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another
 
Join Date: Aug 2019
Posts: 4
jaffa20 is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 08-06-2019, 08:35 AM
NoSparks NoSparks is offline Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another Windows 7 64bit Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 743
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Quote:
Can this be done?
Are you able to record a macro of yourself manually doing what you want to happen when a check box is checked ?

Chances of assistance is greater attaching a workbook rather than pictures of sheet(s) in the workbook.
Reply With Quote
  #3  
Old 08-06-2019, 08:48 AM
jaffa20 jaffa20 is offline Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another Windows 10 Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another Office 2019
Novice
Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another
 
Join Date: Aug 2019
Posts: 4
jaffa20 is on a distinguished road
Default I will try and record macros for each one

Quote:
Originally Posted by NoSparks View Post
Are you able to record a macro of yourself manually doing what you want to happen when a check box is checked ?

Chances of assistance is greater attaching a workbook rather than pictures of sheet(s) in the workbook.
As in record a macro for each individual checkbox? I could try that. Would I be able to have a search for the customer name as part of that?

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
Attached Files
File Type: xlsm Quality Check Recorder.xlsm (36.2 KB, 1 views)
Reply With Quote
  #4  
Old 08-07-2019, 03:31 AM
jaffa20 jaffa20 is offline Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another Windows 10 Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another Office 2019
Novice
Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another
 
Join Date: Aug 2019
Posts: 4
jaffa20 is on a distinguished road
Default 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
Reply With Quote
  #5  
Old 08-07-2019, 08:18 AM
NoSparks NoSparks is offline Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another Windows 7 64bit Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 743
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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.
Attached Files
File Type: xlsm Quality Check Recorder v2.xlsm (42.5 KB, 2 views)
Reply With Quote
  #6  
Old 08-07-2019, 08:35 AM
jaffa20 jaffa20 is offline Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another Windows 10 Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another Office 2019
Novice
Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another
 
Join Date: Aug 2019
Posts: 4
jaffa20 is on a distinguished road
Default

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!!
Reply With Quote
Reply

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
Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another 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
Trying to tie checkboxes in one sheet of an excel doc to a table of specific customers in another Browse to a specific sheet in an excel workbook Sinister Excel 4 04-08-2014 05:53 PM


All times are GMT -7. The time now is 12:55 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft