locked
New to VBA RRS feed

  • Question

  • Hello everyone!

    I am brand new to VBA and still have a lot to learn... So I apologize in advance for what may seem to some of you as a silly question! ;)

    What I am trying to do is the following:

    If a value exists in "B7", the user has to enter an "X" in "F7" OR "G7" OR "H7" AND "J7" OR "K7" OR "L7", and so on for each of the following line through line 18, before the user can save the spreadsheet.

    I already have a VBA macro (that was created by a friend...) so that the user has to enter a value (ranging from 0 to 999999 - I don't know if this information is relevant or not) at least in "B7" (there is a possibility that the only line that will be completed will be line 7). Here's what I have so far:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Broolean, Cancel As Broolean)

    If Sheets("Feuil1").Range("B7") = "" Then

    MsgBox "La cellule B7 n'est pas saisie"

    Cancel = True

    End If

    End Sub

    Private Sub Workbook_Open()

    Sheets("Feuil1").Range("B7").ClearContents

    End Sub

    As you can see, I still have a lot of work to do! Thanks in advance for your precious help! :)

    Thursday, January 31, 2013 2:30 AM

Answers

  • Ok, I think I got it now!! Here's what I came up with:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    With Sheets("Feuil1")
    If .Range("D3") = "" Then
       MsgBox "La date du rôle (cellule D3) n'est pas saisie"
       Cancel = True
    Else
       If .Range("J3") = "" Then
          MsgBox "Votre nom (cellule J3) n'est pas saisi"
          Cancel = True
        Else
           If .Range("B7") = "" Then
              MsgBox "Le numéro de dossier (cellule B7) n'est pas saisi"
              Cancel = True
            Else
               If .Range("F7") & .Range("G7") & .Range("H7") = "" Then
                  MsgBox "Le groupe (cellule F7, G7 ou H7) n'est pas saisi"
                  Cancel = True
               Else
                  If .Range("J7") & .Range("K7") & .Range("L7") = "" Then
                     MsgBox "La complexité (cellule J7, K7 ou L7) n'est pas saisie"
                     Cancel = True
                  Else
                     If .Range("M7") & .Range("N7") & .Range("O7") = "" Then
                        MsgBox "L'article 59 (cellule M7, N7 ou L7) n'est pas saisi"
                        Cancel = True
                     Else
                        If .Range("P7") & .Range("Q7") & .Range("R7") = "" Then
                           MsgBox "La recommandation (cellule P7, Q7 ou R7) n'est pas saisie"
                           Cancel = True
                         End If
                     End If
                  End If
               End If
            End If
       End If
    End If
    End With
    End Sub

    __________________________________________________________________

    Private Sub Workbook_Open()

    Sheets("Feuil1").Range("D3").ClearContents

    End Sub

    If the user tries to save the document, the Message Box appears everytime the requested info is not there, so I think it's done!

    Thanks everyone for your help! :)

    • Marked as answer by BettyBoop70 Thursday, January 31, 2013 4:26 PM
    Thursday, January 31, 2013 4:26 PM

All replies

  • I forgot to mention that I work with Excel 2000 (I know, I know... Don't get me started... Where I work, updates are NOT a priority...!!)

    Thanks again!

    Thursday, January 31, 2013 2:35 AM
  • Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
        Dim bOrA As Boolean
        Dim bOrB As Boolean
        Dim bAnd As Boolean
        
        Dim i As Long
        
        Const cUpTo As Long = 10
        Const cFrom As Long = 7
        
        With Sheets("Feuil1")
            'Not compared with "" as formula result can be ""
            For i = cFrom To cUpTo
                If Len(.Range("B" & i).Value) > 0 Then
                    bOrA = WorksheetFunction.CountIfs(.Range("f" & i & ":h" & i), "X") > 0
                    bOrB = WorksheetFunction.CountIfs(.Range("j" & i & ":l" & i), "X") > 0
                    bAnd = (bOrA And bOrB)
                    If Not (bAnd) Then
                        Cancel = True
                        MsgBox "Pls enter ""X"" in " & IIf(bOrA, "J" & i & ":L" & i & " Block", _
                            "F" & i & ":H" & i & " Block")
                    End If
                End If
            Next i
        End With
    End Sub
    

    See if it helps.

    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Thursday, January 31, 2013 7:55 AM
    Answerer
  • Thanks for your reply and suggestion. However, its giving me an error message (I'll try to translate it as best as I can, as I am working with a French version...)

    Compilation error: Ambiguous name detected: Workbook_BeforeSave

    I have modified what I have already as a code, so here it is (maybe that's the problem?):

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    With Sheets("Feuil1")
    If .Range("D3") = "" Then
       MsgBox "La cellule D3 n'est pas saisie"
       Cancel = True
    Else
       If .Range("J3") = "" Then
          MsgBox "La cellule J3 n'est pas saisie"
          Cancel = True
       End If
    End If
    End With
    End Sub

    _________________________________

    Private Sub Workbook_Open()

    Sheets("Feuil1").Range("D3").ClearContents

    End Sub

    I copied what you suggested under this last "End Sub", is that OK? Should I copy it within what is already in place?? As I said, this is my very first attempt at VBA, so to say I'm lost is an understatement... :S

    Thanks again for your precious help! :)

    Thursday, January 31, 2013 2:49 PM
  • Ok, I think I got it now!! Here's what I came up with:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    With Sheets("Feuil1")
    If .Range("D3") = "" Then
       MsgBox "La date du rôle (cellule D3) n'est pas saisie"
       Cancel = True
    Else
       If .Range("J3") = "" Then
          MsgBox "Votre nom (cellule J3) n'est pas saisi"
          Cancel = True
        Else
           If .Range("B7") = "" Then
              MsgBox "Le numéro de dossier (cellule B7) n'est pas saisi"
              Cancel = True
            Else
               If .Range("F7") & .Range("G7") & .Range("H7") = "" Then
                  MsgBox "Le groupe (cellule F7, G7 ou H7) n'est pas saisi"
                  Cancel = True
               Else
                  If .Range("J7") & .Range("K7") & .Range("L7") = "" Then
                     MsgBox "La complexité (cellule J7, K7 ou L7) n'est pas saisie"
                     Cancel = True
                  Else
                     If .Range("M7") & .Range("N7") & .Range("O7") = "" Then
                        MsgBox "L'article 59 (cellule M7, N7 ou L7) n'est pas saisi"
                        Cancel = True
                     Else
                        If .Range("P7") & .Range("Q7") & .Range("R7") = "" Then
                           MsgBox "La recommandation (cellule P7, Q7 ou R7) n'est pas saisie"
                           Cancel = True
                         End If
                     End If
                  End If
               End If
            End If
       End If
    End If
    End With
    End Sub

    __________________________________________________________________

    Private Sub Workbook_Open()

    Sheets("Feuil1").Range("D3").ClearContents

    End Sub

    If the user tries to save the document, the Message Box appears everytime the requested info is not there, so I think it's done!

    Thanks everyone for your help! :)

    • Marked as answer by BettyBoop70 Thursday, January 31, 2013 4:26 PM
    Thursday, January 31, 2013 4:26 PM