Solved Excel VBA Assistance for pwbk module

May 12, 2017 at 11:14:49
Specs: Windows 7
I need VBA assistance with code that will immediately autofill the specific value “NOT APPLICABLE” in Column W, whenever a report user selects the “None” drop down from a data validation list in the adjacent Column V. (If the report user subsequently changes the "None" selection in Column V, to a different drop down selection, then the text that had been autofilled in Column W, should be cleared out (replaced with nothing.) Columns V and W are fixed column locations and although data will always start on Row 10, the total number of rows is variable. I can do the offset autofill within the main Sub so it updates when the code runs, but I have not been able to get the offset autofill to update in real time (when the user makes a Column V selection.

This code will be a Private Sub run within a VBA Personal Workbook module. (It is used on multiple spreadsheets and is used by multiple users, so it is not feasible to set up forms, combo boxes or to embed the code within each individual xlsm file.) I know there is a By Val option but I don’t think it works with Personal.xlsb modules (I may be mistaken about that.) I have a different private sub that does real time conditional formatting changes to cell font colors within a Personal wb module, so I’m hoping the real time offset autofill will also be possible. Any assistance anyone can provide is greatly appreciated!

message edited by User444


See More: Excel VBA Assistance for pwbk module

Report •

✔ Best Answer
May 13, 2017 at 16:22:24
I do understand (somewhat, and I think enough) what you are trying to do. I have never tried the copying of code from one workbook to modules in another workbook, but it sounds like these links explain how to do it, as well as pointing out some of the gotchas:

https://www.rondebruin.nl/win/s9/wi... (AFAIK Ron de Bruin is a serious Excel & VBA guru)

http://stackoverflow.com/questions/...

I DAGS for how to copy vba code via vba and got those hits, as well as many others.

I'd be interested to hear how you make out. I'll expect an answer as well as the complete code within the hour. ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
May 12, 2017 at 14:52:16
re: "I have a different private sub that does real time conditional formatting changes to cell font colors within a Personal wb module"

Would you mind posting that code? Perhaps we can adapt it for you instead of starting from scratch. That said, the "standard" way of doing the automatic entry/clearing of text as you asked is with a Worksheet_Change macro, e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Cells.Count = 1 Then
    If Target.Column = 22 Then
      If Target = "None" Then 
          Cells(Target.Row, 23) = "Not Applicable"
     Else: Cells(Target.Row, 23) = ""
     End If
   End If
  End If
End Sub

Since you apparently don't want a Worksheet_Change macro, I'd like to see how you are doing "real time conditional formatting" from within a personal.xlsm file.

If all else fails, you could try using Application Level Events as described here:

http://www.cpearson.com/excel/appev...

I've never used Application Level Events so I'm not sure if it fits your situation.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 12, 2017 at 15:59:50
Hi, thank you for your reply. Below is code that will change a cell font color to red, if the user
adds [brackets] at the beginning and end of the text within any cell in Columns U-W. After the
code is run, if the user adds brackets to the beginning and end of any Col U-W cell, the font
color instantly changes to red, if one of those brackets is removed or no longer appears in
first and last position, the cell color reverts to black. I modified the code a bit before sending
to you, so it works without other code in my main sub. Thank you in advance for your expertise!

Sub Format_Bracketed_Text()
     
    Dim wb As Workbook
    Dim pstrColumns As String    
    Dim intOuterLoop As Integer
    Dim intInnerLoop As Integer
    Dim lngCol As Long
    Dim varArray As Variant
    Dim strFormula As String
    Dim strAddress As String
    Dim strCol As String
    Dim strArrayItem As String
    Dim intLoopStart As Integer
    Dim intLoopEnd As Integer
    Dim intHyphenPos As Integer
       
    Set wb = ActiveWorkbook
    pstrColumns = "21 - 23"    'limited to only run on cols u-w
    Set ActiveSheet.pstrColumns = "5 - 25"    

    Const CON_STR_FORMULA_TEMPLATE As String = "=AND(MID($REPL_COL1, 1, 1)=""["", MID($REPL_COL1, LEN($REPL_COL1), 1)=""]"")"
    Const CON_STR_REPL_COL As String = "REPL_COL"

    ActiveSheet.Cells.FormatConditions.Delete
    varArray = Split(pstrColumns, ",")
    
    'Loop through the column index array
    For intOuterLoop = 0 To UBound(varArray)

        'Get the current array item
        strArrayItem = Trim$(varArray(intOuterLoop))
        
        'Check whether the value is numeric
        If IsNumeric(strArrayItem) Then
            'Get the next numeric index
            lngCol = CLng(strArrayItem)
            intLoopStart = lngCol
            intLoopEnd = lngCol
        Else
            'Get the position of the hyphen within the string
            intHyphenPos = InStr(2, strArrayItem, "-")
            
            'Check whether the value contains a hyphen
            If intHyphenPos > 0 Then
                'Initialize loop starting point
                intLoopStart = RTrim$(Left$(strArrayItem, intHyphenPos - 1))
                
                'Initialize loop ending point
                intLoopEnd = LTrim$(Right$(strArrayItem, Len(strArrayItem) - intHyphenPos))
            Else
                Err.Raise vbObjectError + 512, , _
                          "'pstrColumns' input parameter of this procedure contains an invalid value."
                Exit Sub
            End If
        End If
        
        'Loop through current set of column indexes
        For intInnerLoop = intLoopStart To intLoopEnd

            'Set the column index equal to the loop number
            lngCol = intInnerLoop
            
            'Get a column address so the column letters can be extracted
            strAddress = ActiveSheet.Cells(1, lngCol).Address
            
            'Extract column letters from the address
            strCol = Replace(Left$(strAddress, Len(strAddress) - InStr(1, strAddress, "$")), "$", vbNullString)
            
            'Construct conditional formatting formula for the column
            strFormula = Replace(CON_STR_FORMULA_TEMPLATE, CON_STR_REPL_COL, strCol)
            
            With ActiveSheet.Cells(1, lngCol).EntireColumn.FormatConditions.Add(xlExpression, , strFormula)
                .Font.Color = -16776961      ' red font
            End With
            DoEvents
        Next intInnerLoop
        Next intOuterLoop
    
End Sub


Report •

#3
May 12, 2017 at 20:01:34
OK, so I have some comments/questions.

Comment: I guess your definition of a "private sub that does real time conditional formatting changes to cell font colors" is different than mine. The way I see it is that the sub isn't doing the conditional formatting in real time, it's merely applying a CF rule to the specified columns. It's the CF feature that does formatting by applying the rule in real time. Perhaps I'm missing something in what you meant.

Comment: I get an "Object Doesn't Support this Property or Method" at this line:

Set ActiveSheet.pstrColumns = "5 - 25"

I'm not surprised since pstrColumns is a variable, not a VBA Property or Method.

If I comment out that line, the rest of the code runs fine and the CF rule is applied.

Question: You mentioned that you modified the code to run as a standalone macro, so maybe this question doesn't apply, but...

Doesn't this (much shorter) code do the same thing as yours?

Sub ApplyCF()
Dim lngCol As Long
Dim strCol As String

'Clear Existing CF
   ActiveSheet.Cells.FormatConditions.Delete
   
'Loop through Columns U-W, Determine Col Letter, Apply CF
   For lngCol = 21 To 23
     strCol = Replace(Cells(1, lngCol).Address(0, 0), 1, "")
        Columns(lngCol).FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(Left($" & strCol & "1, 1)=""["", Right($" & strCol & "1, 1)=""]"")"

         With Columns(lngCol).FormatConditions(1).Font
              .Color = -16776961
              .TintAndShade = 0
         End With
   Next
End Sub

Question: Are the Columns V & W that you are asking about regarding the "Not Applicable" the same V & W that you are using the [ ] CF macro for?

The reason I ask is this:

Could you write a macro to pre-fill the V cells with Not Applicable and then apply a CF rule to change the Font color to White whenever W doesn't contain None? Similar to the macro for the [ ] CF, have the code apply a =W1 <> "None" rule.

Other than that, I don't know of a way to place a value in a cell based on a user action other than with a Worksheet_Change macro. That said, I have read some stuff about using code to copy code from one module to another. So, follow along with me here:

Remember the Application Level Events that I mentioned earlier? After a brief read of that article, here is what I just tried:

I placed the following code in the ThisWorkbook module of my personal.xlsm file:

Private WithEvents App As Application

Private Sub Workbook_Open()
    Set App = Application
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    OpenMsgTest
End Sub

I then placed this simple macro in a standard module in personal.xlsm:

Sub OpenMsgTest()
  MsgBox "Workbook Opened!"
End Sub

Now, every time I open any workbook I get a "Workbook Opened!" message. That shows that we can use the personal.xlsm file to run a macro based on an Application level event.

OK, based on that test, it seems to me that you might be able to replace my OpenMsgTest macro with a macro that copies code from a personal.xlsm module into the sheet module where you want to the "Not Applicable" stuff to happen. That could happen whenever that other workbook is opened. For example, perhaps you could copy a Worksheet_Change macro into that sheet and then delete it later with the BeforeClose event and the code found in this thread:

http://stackoverflow.com/questions/...

That assumes that you actually want to delete it. Maybe you could copy it into the sheet once and then just check to see if it already exists next time. That would save the work required to manually put it there for every user.

Granted, I've not tried any of this, but the logic seems valid, and we now know that Application Level Events can be triggered from with personal.xlsm, so maybe you can put the pieces together and make it work.

Let me know what you think.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions

#4
May 13, 2017 at 16:07:37
Thanks for the great feedback and info! The conditional formatting code I provided was extracted from a different macro and in that case, the main sub it relates to sets differing col values for the pstr variable, on 10 different worksheets within that workbook. I tried to adapt it to run as standalone Sub within a Personal WB module (under Personal.xlsb project) … it sounds like I should have explained that better since you may have loaded it in a code module within a workbook, vs the Personal WB. In any case, the revised CF code you provided works great and is much more streamlined for the conditional formatting procedure, thank you!

Some additional background on the use case for the offset autofill: Different teams, in different locations, download csv reports each day from a corporate tool. I provide VBA code to these people so the person who initially downloads the csv file will run the personal wb macro.

I don’t have access to the workbooks of the people who initially download the csv reports and run the Personal WB code so I cannot manually add the worksheet change event to their workbook sheets. With the info you provided, it is clear that a Personal wb module can effectively call code that already exists in ThisWorkbook (or a Sheet within a WB, I believe) , so the issue is how to get the below change event code to be pasted into a user’s wb Sheet, when they run the macro code in their Personal WB module. (Once the code is in their Sheet object, it does not need to be deleted.) Are you aware of a way that I could include the below code in the Personal WB as a Private Sub, and have it copied to a particular worksheet object, so the event will be activated?

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Cells.Count = 1 Then
    If Target.Column = 22 Then
      If Target = "None" Then 
          Cells(Target.Row, 23) = "Not Applicable"
     Else: Cells(Target.Row, 23) = ""
     End If
   End If
  End If
End Sub

Sorry if I’m not explaining this well, or if there is a simple solution that I’m missing. Please let me know if this does not make sense, or if you have any other suggestions. Thank you again!!

message edited by User444


Report •

#5
May 13, 2017 at 16:22:24
✔ Best Answer
I do understand (somewhat, and I think enough) what you are trying to do. I have never tried the copying of code from one workbook to modules in another workbook, but it sounds like these links explain how to do it, as well as pointing out some of the gotchas:

https://www.rondebruin.nl/win/s9/wi... (AFAIK Ron de Bruin is a serious Excel & VBA guru)

http://stackoverflow.com/questions/...

I DAGS for how to copy vba code via vba and got those hits, as well as many others.

I'd be interested to hear how you make out. I'll expect an answer as well as the complete code within the hour. ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question