If then statement to go to ElseIf even if first condition is true

Ana_P

New Member
Joined
Aug 22, 2019
Messages
17
Hello

I have a workbook with a userform that I have created to input data into multiple single cell named ranges. Part of my code checks if the data input into the userform is the same as the data already in the named ranges. I need the code to check ALL of the named ranges to the client input data but perform 2 different actions depending on which textbox on the userform is changed. I initially had all the conditions under the one If statement but I don't need the header/footer code to run if the "entity type" or "period end" is changed (just the named range to be updated). If all of the data matches then I want it to exit sub. The code I have is as follows:

VBA Code:
Private Sub CheckDetails()
    Dim Etype As String
    Dim Cname As String
    Dim Ccode As String
    Dim PrepBy As String
    Dim RevBy As String
    Dim PeriodEnd As String
    
    Etype = EntityTypeComboBox.Value
    Cname = ClientNameTextBox.Value
    Ccode = UCase(ClientCodeTextBox.Value)
    PrepBy = UCase(PreparedByTextBox.Value)
    RevBy = UCase(ReviewedByTextBox.Value)
    PeriodEnd = CDate(PeriodEndTextBox.Value & " " & YearTextBox.Value)
    
    If Etype <> Range("Entity_Type").Value Or PeriodEnd <> Range("Period_End").Value Then
    
    'Transfer information into workbook
    Range("Entity_Type").Value = Etype
    Range("Period_End").Value = Format(PeriodEnd, "dd mmmm yyyy")
    
    ElseIf Cname <> Range("Client_Name").Value Or Ccode <> Range("Client_Code").Value Or _
            PrepBy <> Range("Prepared_By").Value Or RevBy <> Range("Reviewed_By").Value Then
    
    'Transfer information into workbook
    Range("Client_Name").Value = Cname
    Range("Client_Code").Value = Ccode
    Range("Prepared_By").Value = PrepBy
    Range("Reviewed_By").Value = RevBy
    
    'Update info on each sheets' header and footer
    Dim ws As Worksheet
    Application.ScreenUpdating = False
        For Each ws In ActiveWorkbook.Worksheets
            ThisWorkbook.UpdateHeaderFooter ws
        Next ws
    Application.ScreenUpdating = True
    Else: Exit Sub
    End If
    
End Sub

Hoping I've explained my scenario well enough. If someone can help me get to the bottom of this it will be greatly appreciated because I've done my head in!
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
You can only run an ElseIf when the first If is False. In order to run both you need to end the first If and start a new one for the second condition.
 

Ana_P

New Member
Joined
Aug 22, 2019
Messages
17
You can only run an ElseIf when the first If is False. In order to run both you need to end the first If and start a new one for the second condition.
I understand that which is why I'm asking for a different way to write my code so that I can accomplish what I'm after. I don't want to end the first if because I want it to exit sub if all the named ranges agree to the client input data on the userform.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
As far as I can see, your Exit Sub is superfluous, it is the last line of the code so the sub will end anyway.
By doing it the way that I said, if the first 2 ranges match then that section will be skipped over, if they don't match then it will run.
The same applies to the second set of ranges, if they all match then that section will be skipped, otherwise it will run.
If all of the ranges in both sections match then everything will be skipped (nothing will happen and the sub will exit naturally).

Try this with the 4 possible combinations of change on a test copy of your workbook to see which (if any) of them are not doing as expected.
1 No change in either section.
2 Changes in both sections.
3 Changes in section 1 only.
4 Changes in section 2 only.

Section 1 refers to entity and period end, section 2 refers to the rest.

VBA Code:
Private Sub CheckDetails()
    Dim Etype As String
    Dim Cname As String
    Dim Ccode As String
    Dim PrepBy As String
    Dim RevBy As String
    Dim PeriodEnd As String
    
    Etype = EntityTypeComboBox.Value
    Cname = ClientNameTextBox.Value
    Ccode = UCase(ClientCodeTextBox.Value)
    PrepBy = UCase(PreparedByTextBox.Value)
    RevBy = UCase(ReviewedByTextBox.Value)
    PeriodEnd = CDate(PeriodEndTextBox.Value & " " & YearTextBox.Value)
    
    If Etype <> Range("Entity_Type").Value Or PeriodEnd <> Range("Period_End").Value Then
    
    'Transfer information into workbook
    Range("Entity_Type").Value = Etype
    Range("Period_End").Value = Format(PeriodEnd, "dd mmmm yyyy")
    
    End If
    
    If Cname <> Range("Client_Name").Value Or Ccode <> Range("Client_Code").Value Or _
            PrepBy <> Range("Prepared_By").Value Or RevBy <> Range("Reviewed_By").Value Then
    
    'Transfer information into workbook
    Range("Client_Name").Value = Cname
    Range("Client_Code").Value = Ccode
    Range("Prepared_By").Value = PrepBy
    Range("Reviewed_By").Value = RevBy
    
    'Update info on each sheets' header and footer
    Dim ws As Worksheet
    Application.ScreenUpdating = False
        For Each ws In ActiveWorkbook.Worksheets
            ThisWorkbook.UpdateHeaderFooter ws
        Next ws
    Application.ScreenUpdating = True
    End If
    
End Sub
 

Ana_P

New Member
Joined
Aug 22, 2019
Messages
17
Thanks Jason. Turns out that does do what I'm after. I have obviously been at this too long! Appreciate your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,679
Messages
5,626,237
Members
416,168
Latest member
DROP_DATABASE_MrExel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top