How to Suppress Warning and Alerts in Excel using VBA

In Microsoft Excel, you can suppress the message warning and alerts especially when you try to create a copy of a worksheet from a workbook containing macros to a new workbook which you wanted to save it as <i>.xls</i> or <i>.xlsx</i> extensions. By that, when you save the document it will prompt you to whether save the work as macro-enabled or macro-free workbook.<br /> <input name="IL_IN_TAG" type="hidden" value="1" /> <br /> <div class="separator" style="clear: both; text-align: center;"> <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9ymOKNotdfLRc5hTmhTMHvCw3zmAxFHz4llhRn_3djoC2T-vSTtyevQiw8oyZa3li-e0O36PucZGAk_6gXJTCrtCs8vnvFWsGmPSBE_TvzRnQhzsSVYxxJ5VDozGEHG14qurj5LlSZzLi/s1600/Suppress+Warning+and+Alerts+in+Excel.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="90" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9ymOKNotdfLRc5hTmhTMHvCw3zmAxFHz4llhRn_3djoC2T-vSTtyevQiw8oyZa3li-e0O36PucZGAk_6gXJTCrtCs8vnvFWsGmPSBE_TvzRnQhzsSVYxxJ5VDozGEHG14qurj5LlSZzLi/s1600/Suppress+Warning+and+Alerts+in+Excel.png" width="320" /></a></div> You can do that by setting <b>DisplayAlerts</b> to <b>False</b> with VBA code.<br /> <br /> <!--INFOLINKS_OFF--> <br /> <pre class="brush: vb">Application.DisplayAlerts = False 'DO SOME TASKS HERE Application.DisplayAlerts = True</pre> <!--INFOLINKS_ON-->

excel microsoft excel VBA How to Suppress Warning and Alerts in Excel using VBA

In Microsoft Excel, you can suppress the message warning and alerts especially when you try to create a copy of a worksheet from a workbook containing macros to a new workbook which you wanted to save it as .xls or .xlsx extensions. By that, when you…

Read more »

How to Create a Custom Checkbox using only the Cells in Microsoft Excel

<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxkACDwIw1FjBEpYlzaGOSEIvJUxFbK77z-KTMBuoeR6qgDNODMyj5g1s-w-DkklQI1Dj1WGWNAnjmfLk3lCHRRomArj4UfnEld1x0-MtMV53CYRiihRzAtz_dyP-KcgFlUG5hFnrCV7o/s1600/Custom+Checkbox+using+Excel+Cells.gif" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="120" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxkACDwIw1FjBEpYlzaGOSEIvJUxFbK77z-KTMBuoeR6qgDNODMyj5g1s-w-DkklQI1Dj1WGWNAnjmfLk3lCHRRomArj4UfnEld1x0-MtMV53CYRiihRzAtz_dyP-KcgFlUG5hFnrCV7o/s1600/Custom+Checkbox+using+Excel+Cells.gif" width="320" /></a></div><div style="text-align: justify;">You might be creating templates with Microsoft Excel, and one of the feature you want is add a check boxes control where users can choose their option. </div><br /><div style="text-align: justify;">We'll, if you want to have another look aside from the default check box control. You can still create and customize your check box controls with a little VBA code to get it done.</div><br /><div style="text-align: justify;">In this example, I have here choices for the reason of a leave application form. I make use of the Microsoft Excel's Cell as a check box. It's just a matter of resizing the rows and columns to make the size of the cell equal. </div><br /><b>1.)</b> Now, let's create first the list of choices, as you can see below I've resized the cells and add borders on the cells which we'll set as a check-cell box control.<br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJzKV7SKFbDsQA2abXmIunZpb3fBZ1UArc_NRlmQpIyItELxBysCXJYEQFSkr-DWuzjmp3DGFEDURzUWpr8_G3nLKK7erbOTjOluKaesDftX_K2fSuVvAx8UFkbe4_IrvbvowbzSHyq9Q/s1600/Check-Cell+Box+Controls.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="127" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJzKV7SKFbDsQA2abXmIunZpb3fBZ1UArc_NRlmQpIyItELxBysCXJYEQFSkr-DWuzjmp3DGFEDURzUWpr8_G3nLKK7erbOTjOluKaesDftX_K2fSuVvAx8UFkbe4_IrvbvowbzSHyq9Q/s1600/Check-Cell+Box+Controls.jpg" width="320" /></a></div><br /><b>2.)</b> Right Click on the Worksheet, then Click "<b><i>View Code</i></b>".<br /><b>3.)</b> Then Copy and Paste the following codes below into the code editor window.<br /><br />The following code will set the Target Range of when the Check-Cell box will be triggered. I set the columns to <b>G</b> &<b> H</b> or it's numeric equivalent <b>7</b> & <b>8</b>. Then create case statement of what rows it will run. The columns and rows may vary depending on your actual template. So you can set it to match your template.<br /><pre class="brush: vb">Private Sub Worksheet_SelectionChange(ByVal Target As Range)<br />    With Target       <br />        'SET THE TARGET COLUMN TO COLUMNS 7 AND 8<br />        If Target.Column >= 7 And Target.Column <= 8 Then<br />            'SELECT CASE OF WHAT ROW WILL BE SELECTED<br />            Select Case Target.Row<br />                Case 19 'SICK LEAVE<br />                    CheckSelected 19, 7<br />                Case 21 'VACATION LEAVE<br />                    CheckSelected 21, 7<br />                Case 23 'PERSONAL<br />                    CheckSelected 23, 7<br />                Case 25 'EMERGENCY<br />                    CheckSelected 25, 7<br />                Case 27 'APPOINTMENT<br />                    CheckSelected 27, 7<br />                Case 29 'OTHERS<br />                    CheckSelected 29, 7<br />                Case Default<br />            End Select<br />        End If<br />    End With<br />End Sub<br /><br /><br /><br />'CHECK IF THE REASON IS CURRENTLY SELECTED<br />Sub CheckSelected(Row As Integer, Col As Integer)<br />    With Cells(Row, Col)<br />        If .Value = "+" Then<br />            .Value = ""<br />            ResetSelectedColor Row, Col<br />        Else<br />            .Value = "+"<br />            ApplySelectedColor Row, Col<br />        End If<br />    End With<br />End Sub<br /><br /><br /><br />'CHANGE THE BACKGROUND OF THE SELECTION<br />Sub ApplySelectedColor(Row As Integer, Col As Integer)<br />    With Cells(Row, Col).Interior<br />        .Pattern = xlSolid<br />        .PatternColorIndex = xlAutomatic<br />        .ThemeColor = xlThemeColorAccent1<br />        .TintAndShade = 0.599993896298105<br />        .PatternTintAndShade = 0<br />    End With<br />End Sub<br /><br /><br />'REMOVE THE BACKGROUND OF THE SELECTION<br />Sub ResetSelectedColor(Row As Integer, Col As Integer)<br />    With Cells(Row, Col).Interior<br />        .Pattern = xlNone<br />        .TintAndShade = 0<br />        .PatternTintAndShade = 0<br />    End With<br />End Sub<br /><br /></pre><br /><b>4.) </b>Now, if you select the Cells it should change the background with the "+" value. You may also change the value to whatever you want.<br /><div><br /></div>

excel microsoft excel How to Create a Custom Checkbox using only the Cells in Microsoft Excel

You might be creating templates with Microsoft Excel, and one of the feature you want is add a check boxes control where users can choose their option. We'll, if you want to have another look aside from the default check box control. You can still cr…

Read more »