VBA code not running automatically

**anwitha** · 07-10-2021, 02:00 AM

Hi All,

I have the below code in my vba project but every time when i changed some data in my sheet columns will not get hide automatically but when i go to VBA property window and press F5 changes will be done in my sheet. can you please help me how to make this work automatically without going and pressing F5 in manually everytime.

Note - I have saved this file as Macro enabled worksheet and its enabled under File>option>trustCentre

Option Explicit

Sub HideColumns()

    Dim Rng As Range, Cell As Range
    
    Application.ScreenUpdating = False
  
    
    Set Rng = Sheets("Schedule").Range("D4:AH4")
    
    Cells.EntireColumn.Hidden = False
    
    For Each Cell In Rng
        If Cell.Value > Sheets("Schedule").Range("A1").Value Then
            Cell.EntireColumn.Hidden = True
   
       End If
    Next Cell

    Application.ScreenUpdating = True
    
End Sub

**BadlySpelledBuoy** · 07-10-2021, 02:03 AM

I can tell you why it's not running automatically, but first you need to stick to the forum rules and wrap the code in your original post in code tags.

BSB

**anwitha** · 07-10-2021, 02:10 AM

sorry....this is done now.

**BadlySpelledBuoy** · 07-10-2021, 02:29 AM

For your code to run automatically it needs a 'trigger'. The way you currently have it set up, that trigger is pressing F5.
What you want to do is use a worksheet change event in the worksheet module. That way code can be triggered when a specified range is altered.

If you need more help than that, attach a sample workbook and explain which range(s) are to trigger the code and we can help more from there.

BSB

**anwitha** · 07-10-2021, 02:37 AM

I am not able to attach the sample due to restriction in my laptop.

I have a calendar dates from 1 to 31st which is there in the cell from D4 to AH4...there are some months where we have only 30 days or 28 day in case of february..in such cases this should not show the columns which is beyond the actual end date in a month.....

but here in my sheet, even if the last working day is 30 its still showing the next column but when i pressed F5 then it will be hidden.

In the code column A1 refers to last day of the month.

**AliGW** · 07-10-2021, 02:39 AM

What restriction in your laptop could possibly be preventing you from uploading a workbook? We are not asking to see real, sensitive data - just a realistic mock-up with a few rows of desensitised data.

**anwitha** · 07-10-2021, 02:46 AM

This is my company provided laptop where in i cannot upload any kind of the documents

**AliGW** · 07-10-2021, 02:47 AM

That's a pain.

**BadlySpelledBuoy** · 07-10-2021, 02:48 AM

From your code I can see that cell A1 holds the information on how many days in the month. Is this something you select manually or is it derived by a formula looking elsewhere? If so, where?

Without a sample workbook there could be lots and lots of questions to get you the result you need. Can you not mock something up from another computer?

BSB

**anwitha** · 07-10-2021, 02:54 AM

Below is the formula avaible in cell A1

=EDATE(DATE(C2,MONTH(1&B2),1),1)-1

In C2 cell i will update the year manually

In B2, cell is dropdown list where in i will select the months like January, February and based on this Cell A1 gives me the last date of the month.

**BadlySpelledBuoy** · 07-10-2021, 03:05 AM

It's difficult to know it will work without a sample workbook but we can try.
Right click on the sheet tab for the "Schedule" sheet and select "View Code".
Paste the code below in that module then see if it works when you change cells B2 or C2.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        If Not Intersect(Target, Range("B2:C2")) Is Nothing Then
            Select Case Day(Application.EoMonth(Range("A1"), 0))
                Case 30
                    Columns("AF:AG").EntireColumn.Hidden = False
                    Columns("AH").EntireColumn.Hidden = True
                Case 29
                    Columns("AF").EntireColumn.Hidden = False
                    Columns("AG:AH").EntireColumn.Hidden = True
                Case 28
                    Columns("AF:AH").EntireColumn.Hidden = True
                Case Else
                    Columns("AF:AH").EntireColumn.Hidden = False
            End Select
        End If
    End If
End Sub

BSB

**anwitha** · 07-10-2021, 03:15 AM

This is working now....Thank you so much for your help

**BadlySpelledBuoy** · 07-10-2021, 03:42 AM

Originally Posted by anwitha

This is working now....Thank you so much for your help

No problem. Happy to help

Thanks for the feedback.

Take care.

BSB

**AliGW** · 07-10-2021, 03:19 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

VBA code not running automatically

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA code not running automatically

Re: VBA code not running automatically

Re: VBA code not running automatically

Re: VBA code not running automatically

Re: VBA code not running automatically

Re: VBA code not running automatically

Re: VBA code not running automatically

Re: VBA code not running automatically

Re: VBA code not running automatically

Re: VBA code not running automatically

Re: VBA code not running automatically

Re: VBA code not running automatically

Re: VBA code not running automatically

Re: VBA code not running automatically

Thread Information

Users Browsing this Thread

Similar Threads

code works running in visual basic editor but not when running from Excel macro

[SOLVED] How to prevent worksheet code from running while Module Macro is running:

[SOLVED] Excel VB code. Message pops up while code running asking question. Code must not wait.

[SOLVED] Automatically returning a date value + 7 days after running some code

Excel 2007 SP2 Objects Resize automatically when running VBA code

Date doesn't appear automatically when running date code

[SOLVED] Automatically running code

Bookmarks

Bookmarks

Posting Permissions