VBA WORKSHEET CHANGE

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
104
Office Version
  1. 365
Platform
  1. Windows
First, please let me clarify that I am not fluent in VBA, but research what I can to do what I need to on occasion.

Here is a link to the sheet for reference, but including the information below as well: <Click here for Workbook>

A little about the sheet: I am trying to figure out how to make different modules run based on specific cell changes in a worksheet change event btu cannot for the life of me figure out what I am doing wrong. On the sheet in question, the first range(s) that i want to trigger one macro is rows 2:36 in columns A,C, E, G, and I, I am trying to avoid refernces to a specific sheet because there will be 13 other sheets with the same structure that will need the same worksheet change event coding. the second range(s) on the sheet that will trigger a different macro is row 2, but every 4th column beginning at X and continuing to AAR. In part of the code I have come up with, I attempted to ensure that once the macro has run, the selected cell on the sheet is the changed cell (either the cell itself if info was deleted, or the row beneath it if something was entered into the cell.) Below is what I have thus far that I cannot get to come together -

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'call module 1 to create a list of all menu items on the sheet
    If Intersect(Target, Range("A2:J36")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Call CombineMenuLists
    'after list is compiled, returns to cell below changed cell if changed cell is not blank
    If Range(Target.Address).Value <> "" Then
    Range(Target.Address).Offset(1, 0).Select
    End If
   'if changed cell is blank, returns to changed cell
    If Range(Target.Address).Value = "" Then
    Range(Target.Address).Select
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True

'call module 2 to create a list of all display names on the sheet
    If Not Intersect(Target, Range("X2:AAU2")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Call DisplayList
    'after list is compiled, returns to cell below changed cell if changed cell is not blank
    If Range(Target.Address).Value <> "" Then
    Range(Target.Address).Offset(1, 0).Select
    End If
    'if changed cell is blank, returns to changed cell
    If Range(Target.Address).Value = "" Then
    Range(Target.Address).Select
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
  End Sub

A little about the macros: for the range(s) that include columns A, C (etc) - the macro is to simply copy the info (from first to last item) in each individual column and paste them into column U. pretty straight forward and I have determined that if I run this macro by itself, it works as intended. so I dont believe this is the issue but here is the coding for review:

VBA Code:
Sub CombineMenuLists()
ActiveSheet.Select
Range("U2", Range("U2").End(xlDown)).Clear
If IsEmpty(Range("A2").Value) = False Then
Range("a2", Range("a2").End(xlDown)).copy
Range("U2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
If IsEmpty(Range("C2").Value) = False Then
Range("C2", Range("C2").End(xlDown)).copy
Range("U2").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
If IsEmpty(Range("E2").Value) = False Then
Range("E2", Range("E2").End(xlDown)).copy
Range("U2").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
If IsEmpty(Range("G2").Value) = False Then
Range("G2", Range("G2").End(xlDown)).copy
Range("U2").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
If IsEmpty(Range("I2").Value) = False Then
Range("I2", Range("I2").End(xlDown)).copy
Range("U2").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
End Sub

The second macro will to run for cahnges in every 4th column of row 2 beginning at X is as follows:

VBA Code:
Sub DisplayList()
ActiveSheet.Select
Range("t17:t400").Value = ""
Dim rRange As Range
Dim rEveryNth As Range
Dim lRow As Long
With ActiveSheet
Set rRange = .Range("S17:S716")
End With
For lRow = 1 To rRange.Rows.Count Step 4
If lRow = 1 Then
Set rEveryNth = rRange(lRow, 1)
Else
Set rEveryNth = Union(rRange(lRow, 1), rEveryNth)
End If
Next lRow
Application.Goto rEveryNth
    Selection.copy
    Range("T17").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    ActiveSheet.Range("$T$16:$T$191").AutoFilter Field:=1, Criteria1:="<>"
    Range("t17", Range("t17").End(xlDown)).copy
    Range("V2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    ActiveSheet.Range("$T$16:$T$191").AutoFilter Field:=1
End Sub

I realize all this information is likely extra, but am trying to be clear what is happening so sorry. Thank you in advance to anyone who can help! I greatly appreciate it. As a last resort, I was simply going to resort to command buttons, but was trying to avoid the chance that someone may neglect to use them in the event information was changed,

Sincerely,

Robert
 
Last edited by a moderator:

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
104
Office Version
  1. 365
Platform
  1. Windows
OK. I think I have the first part done. Now to the second part.

If the data from every fourth column in X2:AAU2 can be copied directly to column V, it sounds like you don't need the data to be copied to S17:S716 and then to T16:T192 and then filter T16:T192 to copy to column V. Is this correct? Can I simply copy directly to column V leaving S17:S716 and T16:T192 blank?
Absolutely, I just didn't know how to accomplish it.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Click here to download your file. Please note that I have made several changes in formatting and I have also deleted unused columns and rows which now makes the file much smaller. Please use this file going forward because if you try the macro on a different file it may not work properly. If you delete a value, the cursor will remain in that cell. If you add a value and press the RETURN key, the cursor will be placed in the cell below. Give it a try and see how it works out.
 

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
104
Office Version
  1. 365
Platform
  1. Windows
Click here to download your file. Please note that I have made several changes in formatting and I have also deleted unused columns and rows which now makes the file much smaller. Please use this file going forward because if you try the macro on a different file it may not work properly. If you delete a value, the cursor will remain in that cell. If you add a value and press the RETURN key, the cursor will be placed in the cell below. Give it a try and see how it works out.
Currently not in a position to look it over, but wanted to thank you for your assistance nonetheless. When I get home later this evening I will check it out. Thank you again for your time, I sincerely appreciate your efforts!
 

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
104
Office Version
  1. 365
Platform
  1. Windows
YOU ARE AMAZING!! This is far more than I was hoping for when I was asking for help. I can't begin to comprehend what you have written, but it works flawlessly and is exactly what I've been trying to accomplish for WEEKS! I cannot thank you enough! I truly do appreciate this, it's magnificent!
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238

ADVERTISEMENT

You are very welcome. :)
 
Solution

tdinsdale

New Member
Joined
Feb 9, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Hi, I was following this thread and also had a question of my own.

I work for a company that underwrites loans on commercial real estate. All the work is conducted in Excel and the workbook we currently use is dated. I'm attempting to revamp the workbook but am rather new to Macros.

My goal is to have a macro run from a cell input. The cell input is the type of collateral for the loan (Retail, Multifamily, Industrial). Using the input, the macro would then change a different sheet with how we value the real estate and financial inputs. For example, if the input was "retail" the 'Pro Forma' tab would change a range of cells to be divided by square feet or if the input was "Multifamily" the range would be divided by units.

My thought process was to create a macro that adjusted the Pro Forma tab to the pro forma used to value the real estate and then reference that macro in the code.

The problems I've run into:

I'm starting to understand how to run a macro when a cell is changed, "If Target.Address = Range("B73").Address Then". However, I'm having difficulty calling on the macro to change based on the input.

I might be in over my head but was hoping there might be a chance. Thanks for any help,

Tim
 

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
104
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I'm a novice personally, but calling a macro is fine simply by
Call "macro"
Where macro is the title in the sub of the macro, not the actual macro number. So for example, when you create the macro, it starts with sub, then your title, then (), to call that macro
Simply enter call, followed by a space, then the title of that macro.
If you were looking to do anything more, I might have misunderstood.
 

tdinsdale

New Member
Joined
Feb 9, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
That helps but I'm attempting to call the macro from a input in a certain cell.

i.e. = C47 = "Retail" Runs Macro_Retail on the Pro Forma Tab
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Could you upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 

tdinsdale

New Member
Joined
Feb 9, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Mumps -

Yes, I'll get it organized and send it over shortly.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,808
Messages
5,627,010
Members
416,214
Latest member
boston814

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top