none
How to do "If cell contains" in VBA

    Question

  • Hi,

      I'm looking for the code to do an if statement when a cell contains "CHECKED OUT"
      I have been using the code below but of course it reads the whole contents of the cell and the cell with say CHECKED OUT by ...

    If ActiveSheet.Range("C6").Value = "CHECKED OUT"

    Thanks,

    Richard
    Monday, September 07, 2009 1:37 PM

Answers

All replies

  • Hi, you need to use the INSTR function e.g.

    If INSTR(1,ActiveSheet.Range("C6").Value , "CHECKED OUT")>0 then
    • Marked as answer by LWCARAB Monday, September 07, 2009 3:19 PM
    Monday, September 07, 2009 1:49 PM
  • ADG already got you fixed but i will post this anyway.  may help in other ways.

    Dim celltxt As String
    celltxt = ActiveSheet.Range("C6").Text
    If InStr(1, celltxt, "CHECKED OUT") Then
    MsgBox ("found it")
    Else
    MsgBox ("no")
    End If
    FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial
    • Marked as answer by LWCARAB Monday, September 07, 2009 3:19 PM
    Monday, September 07, 2009 1:57 PM
  • Thanks, that did just what I wanted!
    Monday, September 07, 2009 2:16 PM
  • I used your code to get the VBA message and it works... too well. Now each time that I try to enter a text in another cell, then the message from the VBA message appears again.

    I tried to implement Boolean but I am too new to VBA. 

    Here is my code (I used Excel 2013 but my colleagues use 2010)

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim celltxt As String
    celltxt = ActiveSheet.Range("E39").Text
    If InStr(1, celltxt, "P670-Staffing") Then

    MsgBox "Add the job title and type of hire in the description cell " & vbNewLine & vbNewLine & "If this is a new position, please obtain HR approval"

    End If

    End Sub

    Thank you for your help

    Tuesday, April 12, 2016 7:31 PM
  • I don't know if the last part was answered but you are using the event handler to trigger the macro. So in your code above the Marco will run whenever you click on a cell.

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    One way to stop this is to check what cell is being changed and if it is not "E39" {from above celltxt = ActiveSheet.Range("E39").Text}  then you can exit. Maybe try something like.

    Dim celltxt As String

     If Target.Address = WorkSheets("the work sheet you are in").Range("E39").address then

     celltxt = ActiveSheet.Range("E39").Text
    If InStr(1, celltxt, "P670-Staffing") Then

    MsgBox "Add the job title and type of hire in the description cell " & vbNewLine & vbNewLine & "If this is a new position, please obtain HR approval"

    else

    exit sub

     End If

    Thursday, September 01, 2016 7:35 PM
  • Something like this should work for you.

    Sub testing()
        Dim docCheckOut As String
        'docCheckOut = "//office.bt.com/sites/Training/Design Admin/Training Plan/adamsmacro.xlsm"
        docCheckOut = "http://excel-pc:43231/Shared Documents/ExcelList.xlsb"
        Call UseCheckOut(docCheckOut)
    End Sub
     
    Sub UseCheckOut(docCheckOut As String)
         ' Determine if workbook can be checked out.
        If Workbooks.CanCheckOut(docCheckOut) = True Then
            Workbooks.CheckOut docCheckOut
        Else
            MsgBox "Unable to check out this document at this time."
        End If
    End Sub


    MY BOOK

    Friday, September 02, 2016 8:02 PM