• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Code Activated by Form Controls

P

PaulFogel123

Member
Hello,
I need a simple bit of code that will activate when I select a form control checkbox. In the attached file, when I select the box and the linked cell returns TRUE, I want to paste>copy>values from one range of cells to another location. One detail: I want this triggered only when the linked cell changes from from FALSE to TRUE, not when the file is opened. If you can supply the code, I can adapt it it to my particular needs.
Thanks! Paul
 

Attachments

  • Checkbox VBA.xlsb
    22.4 KB · Views: 5
I'm having a little trouble when I try to work with multiple checkboxes. Can I ask you to put in some code with several checkboxes, all with different linked cells and different paste>value ranges? This will allow me to understand how to construct these. The checkboxes are actually for months of the year; I will have a checkbox for January-December, and as each month gets downloaded and finalized, I want a command triggered in the model. All the months have to work independently.
Thanks so much.
 
Just repeat what you've already done with different rangs. See attached.
There are many ways to do this but you have a lot of control with the attached. If there's a pattern in the copy to/copy from locations you can get the buttons all to call the same macro; it would save you having to make 12 alterations to the code each time you wanted to do things a littel differently.
 

Attachments

  • Chandoo36736Checkbox VBA.xlsb
    17.8 KB · Views: 5
Thanks. Your code works for all three months. But when I add a fourth month, I get this message using Sub stuff():

upload_2017-12-19_10-8-28.png

There's also one named Sub bleugh(). I don't know what these names mean. I also tried CheckBox5_Click for the fourth month, but no luck. What am I doing wrong?
Paul
 
PaulFogel123 said:
Your code works for all three months. But when I add a fourth month, I get this message using Sub stuff():
Click to expand...
Give the new macro a different name - you can't have two macros with the same name.


PaulFogel123 said:
There's also one named Sub bleugh(). I don't know what these names mean
Click to expand...
The names mean absolutely nothing - that's why I used such names. You must assign the macro yourself to the the check box.
 
Last edited:
p45cal said:
Give the new macro a different name - you can't have two macros with the same name.


The names mean absolutely nothing - that's why I used such names. You must assign the macro yourself to the the check box.
Click to expand...

I didn't know that. I get it now. Many thanks.
 
VBA is working great. Thanks again. The code executes and then calculates at each step. I think it will execute much faster if the code suspends automatic calculation and then turns it on when it's finished. Do you know the command for that?
 
I was able to adapt all the code to my needs. Once I see the structure I can duplicate it and modify it. I think this is fascinating. I have one modification I'd really like to add: the code in the attached file copies cells B8:B15 to D8:D15. I want to check if there's anything in B8:B15 first. If it's empty, say by summing to zero, then the sub should end right there with a message like, "There's nothing in there." If it's not blank, the sub should proceed as it does now. It would be similar to how Data Validation would work. Do you know how to do this?
 

Attachments

  • Chandoo36736Checkbox VBA.xlsb
    23.7 KB · Views: 3
Code: 
Sub Month1_Click()
With Range("B8:B15")
  If Range("B5") Then
    If Application.WorksheetFunction.CountBlank(.Cells) < .Cells.Count Then
      Range("D8:D15").Value = .Value
    Else
      MsgBox "Nothing there!"
    End If
  End If
End With
End Sub
 
Thank you, that works quite well. I found that if I converted B8:B15 to a table and inserted the table name in the code to replace B8:B15, it would work. I inserted a few columns to expand the table so that it resembles the table I'm using in my file. Interestingly, the code will then check the entire table to see if there's anything in there.

The table I'm using in the actual file for which I'm adapting your good work is a database. There are entries by month, looking like the table in the attached file.
What I need to add to the code you've created is a way to distinguish whether there is data in the table for specific months that correspond to to the subs for Month1()_Click, Month2()_Click, and so on. In the sample file, instead of checking for anything in the entire table, it would check MonthYear only for Month1 (January).

I guess this code isn't so simple anymore, but this is the last refinement I'd need.

Paul
 

Attachments

  • Checkbox VBA.xlsb
    27.7 KB · Views: 4
Then you need to change it to the likes of:
With Range("Table1[Category]")

BUT… your recent attachment gives me a hint that what you're doing is likely to be domething that is done frequently in commercial circles and leads me to suspect strongly that they way you're going about it is more convoluted than it needs to be, nor is it especially robust.
 
I am unable to figure out how to use this new line of code for Table 2. I tried With Range("Table2[MonthYear]") but I don't know how to qualify only for 12017, for example. Excel and VBA are very different.
Paul
 
Nor am I without seeing realistic data.
If I knew more about what you're trying to do I'd suggest alternative solutions.
 
Here's the code I have so far, adapted from what you've given me:
Sub Month1_Click()
Application.Calculation = xlCalculationManual
If Range("TrueMonth1") Then Range("CMIMonth1paste").Value = Range("CMIMonth1copy").Value
If Range("TrueMonth1") Then Range("MDCMixMonth1paste").Value = Range("MDCMixMonth1copy").Value
If Range("TrueMonth1") Then Range("APCMixMonth1paste").Value = Range("APCMixMonth1copy").Value
If Range("TrueMonth1") Then Range("IPratesMonth1paste").Value = Range("IPratesMonth1copy").Value
If Range("TrueMonth1") Then Range("OPratesMonth1paste").Value = Range("OPratesMonth1copy").Value
If Range("TrueMonth1") Then Range("IPMixMonth1Part1paste").Value = Range("IPMixMonth1Part1copy").Value
If Range("TrueMonth1") Then Range("IPMixMonth1Part2paste").Value = Range("IPMixMonth1Part2copy").Value
If Range("TrueMonth1") Then Range("OPMixMonth1Part1paste").Value = Range("OPMixMonth1Part1copy").Value
If Range("TrueMonth1") Then Range("OPMixMonth1Part2paste").Value = Range("OPMixMonth1Part2copy").Value
If Range("TrueMonth1") Then Range("DischChangeMonth1paste").Value = Range("DischChangeMonth1copy").Value
If Range("TrueMonth1") Then Range("VisitsChangeMonth1paste").Value = Range("VisitsChangeMonth1copy").Value
If Range("TrueMonth1") Then Range("LOSChangeMonth1paste").Value = Range("LOSChangeMonth1copy").Value
If Range("TrueMonth1") Then Range("IncStmtMonth1paste").Value = Range("IncStmtMonth1copy").Value
If Range("TrueMonth1") Then Range("BalSheetMonth1paste").Value = Range("BalSheetMonth1copy").Value
If Range("TrueMonth1") Then Range("CapSpendMonth1paste").Value = Range("CapSpendMonth1copy").Value
If Range("TrueMonth1") Then Range("IncrDecrMonth1paste").Value = Range("IncrDecrMonth1copy").Value
Range("ProductivityMonth1paste").Value = Range("ProductivityMonth1copy").Value
Application.Calculation = xlCalculationAutomatic

End Sub

Month1 is the checkbox linked cell. I have 12 of these subs, one for each month of the year. The next 17 statements copy formula calculations and paste their values into the appropriate places. The destinations are future months on the assumptions tab. Users enter their assumptions into these cells and it drives the forecast model's future months. Like the checkbox in the sample file you saw, the user specifies which months are actuals and which are forecast so the model knows what to do. If it's an actual (historical) month, then actual data overrides the forecast that would otherwise be calculated from the assumptions entered (using IF statements). The assumptions entered for actual months will then have no effect. As each month is closed out, actuals should replace the assumptions that were entered, but you can't have both data entry and formulas at the same time in the same cell. The code nicely solves that.

To give an example: say you think salaries will go up 5% in February. You enter 5% in the appropriate assumptions cell, and the forecast increases salaries by 5% in February. Then February comes around and the books are closed. February's actuals then replace whatever you forecasted for that month. Let's say the salary increase got delayed until April. Yet in the assumptions cell for February still sits the 5% you entered, which has no effect now. It should say 0%, which is the actual result. And if you're using history as a guide for what to enter in your forecast, you want to see 0%, not 5%. So, the code updates this for actual results.

Paul
 
This doesn't really tell me much since you have so many named ranges (at least 34x12?) and I don't know the shape/whereabouts and contents of them.

As an aside that code can probably be more efficient with:
Code: 
Sub Month1_Click()
If Range("TrueMonth1") Then
  Application.Calculation = xlCalculationManual
  Range("CMIMonth1paste").Value = Range("CMIMonth1copy").Value
  Range("MDCMixMonth1paste").Value = Range("MDCMixMonth1copy").Value
  Range("APCMixMonth1paste").Value = Range("APCMixMonth1copy").Value
  Range("IPratesMonth1paste").Value = Range("IPratesMonth1copy").Value
  Range("OPratesMonth1paste").Value = Range("OPratesMonth1copy").Value
  Range("IPMixMonth1Part1paste").Value = Range("IPMixMonth1Part1copy").Value
  Range("IPMixMonth1Part2paste").Value = Range("IPMixMonth1Part2copy").Value
  Range("OPMixMonth1Part1paste").Value = Range("OPMixMonth1Part1copy").Value
  Range("OPMixMonth1Part2paste").Value = Range("OPMixMonth1Part2copy").Value
  Range("DischChangeMonth1paste").Value = Range("DischChangeMonth1copy").Value
  Range("VisitsChangeMonth1paste").Value = Range("VisitsChangeMonth1copy").Value
  Range("LOSChangeMonth1paste").Value = Range("LOSChangeMonth1copy").Value
  Range("IncStmtMonth1paste").Value = Range("IncStmtMonth1copy").Value
  Range("BalSheetMonth1paste").Value = Range("BalSheetMonth1copy").Value
  Range("CapSpendMonth1paste").Value = Range("CapSpendMonth1copy").Value
  Range("IncrDecrMonth1paste").Value = Range("IncrDecrMonth1copy").Value
  Range("ProductivityMonth1paste").Value = Range("ProductivityMonth1copy").Value
  Application.Calculation = xlCalculationAutomatic
End If
End Sub
 
I see where you made changes and they make sense.

I have all those range names to avoid using cell addresses. That way, if I insert a row or column, the code will be unaffected. The "copy" range names are where the formulas are housed. The "paste" range names are the destinations to paste the values from those formulas. I have about 150 different assumptions fields for which users enter their expectations for the future, one of which is salary increases (or decreases). The file is very large even in binary format.
 
You must log in or register to reply here.
Back
Top