I need help Editing this Macro

[Buns1976]

Hi everyone,

New to the forum and very new to Macros. I am trying to get rid of the TRUE dialog box every time I run the macro below and not sure how to accomplish that?

Thanks in advance!!

Sub CheckBox372_Click()

    If ActiveSheet.Shapes(Application.Caller).ControlFormat.Value = 1 Then
        MsgBox Range("E2").Select
    Selection.Copy
    Range("D2").Select
    ActiveSheet.Paste
    Range("K2").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=MID(RC[-7],10,3)+RC[2]"
    Range("K2").Select
Else
        MsgBox Range("D2").Select
    Selection.ClearContents
End If
    
End Sub

 

[Buns1976]

Fluff, YES it works great except instead of a "PASTE" it needs to be a "PASTE SPECIAL-Value" so it doesn't mess up the cell formatting with shading and borders. Then on the "Uncheck" we need to restore the =MID(D2,10,3) with a "PASTE SPECIAL-Formula" in column K and return to Column D like below

Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False    Selection.Copy

Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
[FONT=Verdana]SkipBlanks:=False, Transpose:=False[/FONT]

 

[Fluff]

Ok, how about

Sub Buns1976()
   Dim Shp As Shape
   
   Set Shp = ActiveSheet.Shapes(Application.Caller)
   With Shp.TopLeftCell
      If Shp.ControlFormat.Value = 1 Then
         Cells(.Row, "D").Value = Cells(.Row, "E").Value
         Cells(.Row, "K").FormulaR1C1 = "=MID(RC[-7],10,3)+RC[2]"
         Cells(.Row, "K").Select
      Else
         Cells(.Row, "D").ClearContents
         Cells(.Row, "K").FormulaR1C1 = "=mid(rc[-7],10,3)"
         Cells(.Row, "D").Select
      End If
    End With
End Sub

 

[Buns1976]

Getting closer! These numbers are formatted as text because some have a leading zero like this. 09870221601295. When I run the macro it returns 9870221601295 which messes up the MID plus some other formulas. It wasn't doing that previously!


Edit: When i do a "PASTE SPECIAL-Value" manually it keeps the leading 0 so not sure

 

[Fluff]

OK,

      If Shp.ControlFormat.Value = 1 Then
         Cells(.Row, "E").Copy
         Cells(.Row, "D").PasteSpecial xlPasteValues
         Cells(.Row, "K").FormulaR1C1 = "=MID(RC[-7],10,3)+RC[2]"
         Cells(.Row, "K").Select
      Else

 

[Buns1976]

That my friend appears to be a winner!!! If you something were to change I'll post back and by the way, I found out what happens when the checkbox is ever so slightly outside of the cell in a hurry.
Thank you ever so kindly Fluff!

 

[Fluff]

Glad to help & thanks for the feedback

 

[Buns1976]

Hi again Fluff,
I just realized that the cell the macro is repopulating on the uncheck has to be unprotected which is a huge security issue for me.
Would you have any suggestions on a work around for that other than UnProtecting the sheet?

 

[Fluff]

Add these two lines to the code, the first at the start & the second at the end

ActiveSheet.Unprotect "pword"
ActiveSheet.Protect "pword"

Change pword to whatever you password is

 

[Buns1976]

Awesome Fluff. Excellent!!

 

[Fluff]

You're welcome & thanks for the feedback