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]

Run_time error '1004"

Application-defined or object-defined error

 

[Fluff]

I cannot replicate that error, so am not sure what is going wrong.

 

[Buns1976]

Hi there Fluff,

Just to be clear the check boxes are in F and the formula to restore is in K correct? The code below is from the check, uncheck you did before and that works great!

Thanks again!!

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

 

[Buns1976]

Hi Fluff,

I got it with a little workaround! I restored the original CLEAR_CHECKBOXES macro. Stored the MID formula in an off screen cell and then
did another macro to copy/paste that stored MID to the column. Long way around the barn I guess but it works. I should have thought about
that prior to bothering you!!

Thanks again for your great work!!

 

[wornhall]

Sorry, the above post was to have been for another query.

 

[Buns1976]

Hi Fluff,

A question on the checkboxes I have in this sheet. I'm wondering if there is a way in the underlying
cell to place an "X" so for an example if the checkbox resides over the top of cell G2 if that check box
is checked, it places "X" in G2?

Granted it won't be visible in this sheet but should be when I export it. Right now the G Column shows
that its empty.

Thank you!

[/COLOR]Sub CHECKBOX_Y_N()   ActiveSheet.UNPROTECT "1875"
   
   Dim Shp As Shape
   
   Set Shp = ActiveSheet.Shapes(Application.Caller)
   With Shp.TopLeftCell
     If Shp.ControlFormat.Value = 1 Then
         Cells(.Row, "F").Copy
         Cells(.Row, "E").PasteSpecial xlPasteValues
         Cells(.Row, "L").FormulaR1C1 = "=MID(RC[-7],10,3)+RC[2]"
         Cells(.Row, "L").Select
      Else
         Cells(.Row, "E").ClearContents
         Cells(.Row, "L").FormulaR1C1 = "=mid(rc[-7],10,3)"
         Cells(.Row, "E").Select
      End If
    End With
    ActiveSheet.PROTECT "1875"
    

End Sub[COLOR=#333333]

 

[Fluff]

You can go into properties & link the checkbox to the cell, that will give true or false.
Will that be ok?

 

[Buns1976]

Hi Fluff,

I tried that and it doesn't seem to work?

Thanks

 

[Fluff]

In what way "doesn't it work"

 

[Buns1976]

Hi Fluff,

This is a linked table in Access. So when I open the Excel table to view in Access before appending it, the column is still blank.
What I need is an indication that a particular book is closed if the checkbox is checked.

Thank you!