• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need MsgBox to appear with entry of invalid phrase, need code to run continuously

M

Melowshipslinki

New Member
Very new to Excel/VBA- I only know what little I've taught myself so far. And so far, so good for my purposes- I just need a few more bells and whistles in my workbook to make it more user-friendly. Unfortunately, I cannot attach samples, as the information contained is sensitive and proprietary. Apologies!

In one of my sheets, in my "NOTES" column (H), I need to have a message box appear when one or two certain invalid words/phrases are entered into any cell in that column.

I tried using data validation, but either I don't have the right reverse-logic formula or the tool doesn't allow for data 'invalidation'. I would like to try and do this in VBA though.
I have tried to combine a WorkBook_open function with an Update_Time function and a Worksheet Change event (am I even using the right language to explain?), as I need the code to run continuously as my users enter data. I've been stopped at every turn by various errors.

I do believe I need an If function with the msgbox as the action. Here is an example of what I am trying to accomplish:

In ThisWorkBook, I have entered:

Private Sub Workbook_Open()
Run Update_Time
End Sub

In the VBA editor for the Sheet containing the cells in question, I have entered:

Sub Update_Time()
Application.OnTime Now() + TimeValue("00:00:01"), "WorkSheet_Change"
End Sub
__________________________________________________________________________________
Private Sub WorkSheet_Change(ByVal Target As Range)

If ActiveSheet.Range("H13").Value = "*tah*" Then
MsgBox "Please Replace with ""Absent Since (X)year""", vbOKOnly, "Exclusion Not Specific Enough"
End If

End Sub


Of all the articles I've searched, I can't grasp through staring at the examples, how my particular circumstance should be formatted. I have nothing defined here, I'm getting 'object' errors. If someone could set me up and give me a primitive explanation that would be wonderful! Thank you :)
 
Debraj,

So I did read all of the new users info before posting, but was worried about uploading a sample, as the information I work with is very sensitive/confidential. Will a recreation do? I don't have anything to check honestly, I hardly know where to start with this. I can put together a blank template, but I can't see how that would help as I can't copy the titles, the phrase that would instigate the msgbox, or the message itself...

Is it possible to put me on the right track with this info?

When 'x' is entered into any cell in Range("H:H"), I need a 'stop' message, with only the 'ok' button, to pop-up and say, "please replace 'x' with 'y'". I need this to run continuously so those entering data into my workbook will have to correct this before moving on.

I'm sorry I can't be more specific. Thank you for the guidance!
 
Hi, Melowshipslinki!

Had read carefully the new uses info you'd have read this, under the section Posting a Sample Workbook:
"Important
When Posting data online always remember to anonymise the data, especially names or data if it is commercial or confidential in nature."

So, help us help you and provide the file asked by Debraj(ex-Roy).

Despite of this, a few comments about your code:
- at open time you run the procedure Update_Time
- this procedure schedules at the next second of this procedure: WorkSheet_Change, which is a procedure that holds the code for worksheet cell changes (input that causes change cell values, not recalculate formulas), and this procedure has an argument that's missing in your call, the actual range on which it should operate.

Regards!
 
Yes, I read that.

I must have chosen the wrong venue in which to seek help, as I do not have an excel file housing a working code sample, or any code sample for that matter, to upload- It is simply that I don't know where to start and I was hoping someone could provide me with a template in which to replace surrogate symbols with my actual information.

Without a started VBA project and without any compromising info, the only sample I can provide is a blank spreadsheet, just column H. The range in which I need the procedure to operate is the entirety of column H.

My thought process on this, as a human understanding, is that no other information is needed to explain a template for the procedure I wish to create, just the range (H:H), condition- is 'x' entered, and action (MsgBox "phrase-y"). All other information could be expressed as "sheet1, ThisWorkBook, ActiveSheet.Range(H:H), etc.", no? I would prefer to have this laid out for me in a non-specific way, so I can understand the syntax and be able to recognize it and recreate it in other contexts. Perhaps I am not clearly understanding these functions, their requirements, and their results.

If it is not possible to provide a solution without an information-specific sample, then I will have to make do in some other way.

But thank you regardless, for your guidance.
 
Hi, Melowshipslinki!
Why should a message box will indicate to replace an "x" by a "y"? Why just not it replaces it directly then?
And suppose you implement it within the procedure for the worksheet change event, it will detect users inputting an "x" but it won't never detect it if cells are copied and pasted.
Regards!
 
True, true- What I actually meant was to 'copy and paste' my condition and phrase into the code template, those, in and of themselves, are confidential. Sorry for not being clear on that part!

Because I am not looking at numerical data or data that produces/is produced from any other cell/column on the spreadsheet, nothing in the other cells on the spreadsheet has any affect on the procedure, and there is nothing in any cell in column H to being with- I am simply looking to stop the users from using two specific words in their notes, but a simple instruction in the title has proved ineffective. Is this possible? Thanks!
 
Oh, sorry, I didn't read that correctly- I don't want to replace directly, because I would prefer to have my users see that it is incorrect and make the change themselves, so they get into the habit of discontinuing the use of the flagged words in other systems.

Sorry about that and thank you for bearing with me here!
 
Hi, Melowshipslinki!

Give a look at this file:
https://dl.dropboxusercontent.com/u...sly (for Melowshipslinki at chandoo.org).xlsm

This is the code at the worksheet Hoja1 class module:
Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    ' constants
    Const ksTestRange = "H:H"
    Const ksForbidden = "x"
    Const ksAllowed = "y"
    ' declarations
    ' start
    If Application.Intersect(Range(ksTestRange), Target) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    ' process
    With Target
        If .Value Like "*" & ksForbidden & "*" Then
        ' replace previous for next one, if instead of partial match you want a whole cell match
        'If .Value = ksForbidden Then
            MsgBox "Use of <" & ksForbidden & "> is forbidden; use <" & _
                ksAllowed & "> instead.", _
                vbApplicationModal + vbExclamation + vbOKOnly, "Warning"
        End If
    End With
    ' end
End Sub

Play a bit with it, entering different data, with and without "x" in any column, including H. It works fine, doesn't it? Well, not now try to put a few "x" in any column and copy that range at column H. Useless.

So, if I were you, I'd go for a macro that works as complement of that code, maybe before saving the workbook, that checks all the H column content for bypassed forbidden values and issue the proper message.

Regards!
 
Works great for my purposes! The users are not allowed to copy and paste data, but I'll mess around with a macro to check for any already present invalid entries.

A thousand thank yous, I've been trying to wrap my brain around this for weeks! :)

This lead me to another question, but I will ask in a new thread.
 
Hi, Melowshipslinki!
Glad you solved it, even if being aware to be bulletproof. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
You must log in or register to reply here.
Back
Top