none
IF / AND VBA Help RRS feed

  • Question

  • Hello - 

    I have a button the when clicked I would like to have check a couple of things. Take a look at the screen shot below and what I have pieced together from some other code I received from this site (thank you Jim Cone!)

    Anyhow, pretty sure I am way off base so any assistance or suggestions anyone might have would be much appreciated.

    Lol...Here is what I have attempted thus far:


    Private Sub CloseAttach_Click()

    '''''''''''''''''''''''''''''''''''''''''''''''
    ' Closes, protects and hides form             '
    '''''''''''''''''''''''''''''''''''''''''''''''

        Dim shp As Excel.Shape
        Dim r As Long

        ActiveWindow.DisplayWorkbookTabs = True

        For Each shp In Me.Shapes  'Or For Each shp In Sheets("Attachments").Shapes
        If shp.TopLeftCell.Address = Range("B2:F2").Address And _
            Range("B3:F3") = "" Then
          MsgBox ("Please enter a desricption of the file(s) attached")
            Exit Sub
        Exit For
        End If
        Next
        Sheets("Attachments").Protect Password:="", DrawingObjects:=False, Contents:=True, Scenarios:= _
            True
        Sheets("ICA Coversheet").Visible = True
        Sheets("ICA Coversheet").Select
        Sheets("Attachments").Visible = False

    End Sub

    Thank you :)

    Wednesday, June 29, 2016 5:12 PM

Answers

  • Hi Deepak - 

    Apologies for the delayed response. I tried to play with your suggestion a bit but could not get it to work. I did end up using the single reference multiple times as this actually ended up being more useful in order to accommodate a few other options in the process.

    Thank you for your suggestion and time spent. Your assistance is greatly appreciated :)

    Wednesday, July 13, 2016 9:07 PM

All replies

  • Oh and also...the code I have thus far does work if I use only one cell in the range as shown below...

     For Each shp In Me.Shapes  'Or For Each shp In Sheets("Attachments").Shapes
        If shp.TopLeftCell.Address = Range("C2").Address And _
            Range("C3") = "" Then
          MsgBox ("Please enter a desricption of the file(s) attached")
            Exit Sub
        Exit For
        End If
        Next

    I guess I am looking to incorporate all the cells in a range without having to repeat the above ten (10) times?


    Wednesday, June 29, 2016 5:17 PM
  • Hi rstreets2,

    you want to incorporate all the cells in a range without having to repeat the above ten (10) times?

    I think that you need to include another for each loop inside the loop you had already created.

    you can try to create it like below.

    Sub demo()
    Dim rg As Range, c As Range
    Dim wsSource As Worksheet
    Set wsSource = Sheets("Sheet1")
    Set rg = wsSource.Range("B2:F2")
    For Each c In rg
            '--------your code place here-------
           Debug.Print c.Address
    Next c
    
    End Sub
    

    here I had mentioned the whole sub but you only have to use objects , variables and loop and include in to your code.

    then after you not need to specify the range manually in your code to match instead of that you have to pass "c".

    Note: I have just give you the example how you can use the range in loop.

    you need to modify your code based on your requirement and have to make changes in the code to achieve the requirement.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 30, 2016 2:27 AM
    Moderator
  • Hi Deepak - 

    Apologies for the delayed response. I tried to play with your suggestion a bit but could not get it to work. I did end up using the single reference multiple times as this actually ended up being more useful in order to accommodate a few other options in the process.

    Thank you for your suggestion and time spent. Your assistance is greatly appreciated :)

    Wednesday, July 13, 2016 9:07 PM