VBA Code to Match US Region to the State that is entered

[Paul_Hossler]

I added the .Offset line to clear the rest of the line if Client is blank like in Line 7

Remove this line

.Offset(0, 1).Resize(1, .Parent.Columns.Count - 2).ClearContents

[pawcoyote]

Thank you I will look at that. I am also working on a new sheet that I will want to copy information from the MDS sheet into I am going to add it to a sample and post once done...

Remove this line

.Offset(0, 1).Resize(1, .Parent.Columns.Count - 2).ClearContents

[pawcoyote]

Hi, I have an add on to this now. I have included a new worksheet called MOST. I need to copy specific fields from the "MDS Equipment Detail Worksheet and put them into the MOST worksheet. On the MOST worksheet I have put the Headers from the MDS worksheet and their column letter so you can see what I am trying to copy over. I would like this set as a macro (button) that I can click once I have all the data keyed in properly on the MDS worksheet.

Thank you for any help.

Please see Sample Workbook_18

[Paul_Hossler]

Try this

You might need to tweak it

[pawcoyote]

Thank you very much I will be looking it over and again thank you for all your help.

Try this

You might need to tweak it

[pawcoyote]

Hi,

If I wanted to use the Column Headers for reference on the MOST and MDS worksheet instead of the Column Number do I substitute the number i.e. 1 with the Header name i.e. "Oracle Project Code" on the MDS worksheet and "Project Number" on the MOST worksheet?

wsMOST.Cells(i - 1, "Project Number").Value = .Cells(i, "Oracle Project Code").Value

Additional Question: Why do I get the following when I go to look at my macro's

'Sample Workbook_19.xlsm'!VersionNotes.VersionNotes

Try this

You might need to tweak it

[Paul_Hossler]

1. I added new sub LUV to return the n-th row bu searching the col headers row to find the column string each time


I added error message to help figure out what was wrong with the column header

I would have been a LOT easier if the column headers in MDS and MOST were called the same

Option Explicit

'Look Up value
Function LUV(rColHeaders As Range, sColHeader As String, N As Long) As Range
    Dim i As Long
    
    i = 0
    
    On Error Resume Next
    i = Application.WorksheetFunction.Match(sColHeader, rColHeaders, 0)
    On Error GoTo 0
        
    If i <> 0 Then
        Set LUV = rColHeaders.Parent.Cells(N, i)
    Else
        MsgBox "Column " & sColHeader & " not found in row " & rColHeaders.Address & " on worksheet " & rColHeaders.Parent.Name
    End If
End Function
Function GetColumnNumber(sColHeader As String, rColHeaders As Range) As Long
    Dim i As Long
    i = 0
    
    On Error Resume Next
    i = Application.WorksheetFunction.Match(sColHeader, rColHeaders, 0)
    On Error GoTo 0
        
    If i <> 0 Then
        GetColumnNumber = i
    Else
        MsgBox "Column " & sColHeader & " not found in row " & rColHeaders.Address & " on worksheet " & rColHeaders.Parent.Name
    End If
End Function

Additional Question: Why do I get the following when I go to look at my macro's

Probably because you had 2 workbooks open with the same sheet names and macro names

[pawcoyote]

Thank you I will be working on this today. I have a quick question for you. I keep getting the following Sheet added to my workbook and I don't know why. It is a Very Hidden worksheet.

1. I added new sub LUV to return the n-th row bu searching the col headers row to find the column string each time It goes down 412 Rows and out to Column IV. I had to remove some of the rows to make the file size fit for upload.


I added error message to help figure out what was wrong with the column header

I would have been a LOT easier if the column headers in MDS and MOST were called the same

Option Explicit

'Look Up value
Function LUV(rColHeaders As Range, sColHeader As String, N As Long) As Range
    Dim i As Long
    
    i = 0
    
    On Error Resume Next
    i = Application.WorksheetFunction.Match(sColHeader, rColHeaders, 0)
    On Error GoTo 0
        
    If i <> 0 Then
        Set LUV = rColHeaders.Parent.Cells(N, i)
    Else
        MsgBox "Column " & sColHeader & " not found in row " & rColHeaders.Address & " on worksheet " & rColHeaders.Parent.Name
    End If
End Function
Function GetColumnNumber(sColHeader As String, rColHeaders As Range) As Long
    Dim i As Long
    i = 0
    
    On Error Resume Next
    i = Application.WorksheetFunction.Match(sColHeader, rColHeaders, 0)
    On Error GoTo 0
        
    If i <> 0 Then
        GetColumnNumber = i
    Else
        MsgBox "Column " & sColHeader & " not found in row " & rColHeaders.Address & " on worksheet " & rColHeaders.Parent.Name
    End If
End Function

Probably because you had 2 workbooks open with the same sheet names and macro names

[Paul_Hossler]

No idea why that sheet is being added

Ver 20 does not have it, so it must be something in your real workbook

It references another workbook if that helps

Capture.JPG

[pawcoyote]

Thank you, I will look at what I might have put in...

[pawcoyote]

Hi,

I no longer get the Regions populated when a State is added nor is the Oracle Site Reference being added. I have added version 21 with changes I have made so you can see what I have done so far. Quick note on the error, could JIVE be causing that to happen?

1. I added new sub LUV to return the n-th row bu searching the col headers row to find the column string each time


I added error message to help figure out what was wrong with the column header

I would have been a LOT easier if the column headers in MDS and MOST were called the same

Option Explicit

'Look Up value
Function LUV(rColHeaders As Range, sColHeader As String, N As Long) As Range
    Dim i As Long
    
    i = 0
    
    On Error Resume Next
    i = Application.WorksheetFunction.Match(sColHeader, rColHeaders, 0)
    On Error GoTo 0
        
    If i <> 0 Then
        Set LUV = rColHeaders.Parent.Cells(N, i)
    Else
        MsgBox "Column " & sColHeader & " not found in row " & rColHeaders.Address & " on worksheet " & rColHeaders.Parent.Name
    End If
End Function
Function GetColumnNumber(sColHeader As String, rColHeaders As Range) As Long
    Dim i As Long
    i = 0
    
    On Error Resume Next
    i = Application.WorksheetFunction.Match(sColHeader, rColHeaders, 0)
    On Error GoTo 0
        
    If i <> 0 Then
        GetColumnNumber = i
    Else
        MsgBox "Column " & sColHeader & " not found in row " & rColHeaders.Address & " on worksheet " & rColHeaders.Parent.Name
    End If
End Function

Probably because you had 2 workbooks open with the same sheet names and macro names

[Paul_Hossler]

When I changed it to use column headers instead of column numbers, I had a couple wrong

[pawcoyote]

That would have been on the MDS module correct? I want to be sure I am looking at the right mod for fixes.

[Paul_Hossler]

Yes

The way I made the LUV() function it takes the col header string so look in that area

[pawcoyote]

Hi, I would like to protect the workbook/worksheets once we are done with the coding. But when I do the VBA and Macro's do not run properly. Also I am seeing that the VBA isn't running in excel 2010. I built this on excel 2007. Any suggestions?

Yes

The way I made the LUV() function it takes the col header string so look in that area

[pawcoyote]

I found out what is doing it but not sure why. I use Jive and every time I post the workbook using jive it adds this hidden worksheet. Still looking for a fix..

No idea why that sheet is being added

Ver 20 does not have it, so it must be something in your real workbook

It references another workbook if that helps

Capture.JPG

[Paul_Hossler]

Don't know what Jive is, but maybe don't use it

To allow some cells to be updated when the sheet is protected, you need to make .Locked = False

Option Explicit

Sub ProtectMDS()
    With Worksheets("MDS Equipment Detail")
        If .ProtectContents Then Exit Sub
        Range(.Rows(1), .Rows(6)).Locked = True
        Range(.Rows(7), .Rows(.Rows.Count)).Locked = False
        .Protect Password:="password", UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowDeletingRows:=True, AllowFiltering:=True
    End With
End Sub


Sub UnProtectMDS()
    With Worksheets("MDS Equipment Detail")
        If Not .ProtectContents Then Exit Sub
        .Unprotect Password:="password"
    End With
End Sub

[pawcoyote]

Hi, I added the code to the MDS Equipment Detail Sheet in VBA but I keep getting an error once I protect the sheet.

I need to unprotect the following when we enter the MDS Equipment Detail sheet. Allowing all VBA and Macros to run...

Row 5 & 6 Certain Headers Columns CA - CL (I want to be able to change the names of the headers as needed

Row 7 and on all fields to be open for running VBA and Macros..

I am also looking to only copy the information from the MDS Equipment Detail Sheet to the MOST Add Sheet when a Client Name is filled in the Client Name column otherwise do not copy those rows..

When I protect the MDS Equipment Detail sheet this is where the error goes too..

With rMDS
.Interior.Color = xlNone

Don't know what Jive is, but maybe don't use it

To allow some cells to be updated when the sheet is protected, you need to make .Locked = False

Option Explicit

Sub ProtectMDS()
    With Worksheets("MDS Equipment Detail")
        If .ProtectContents Then Exit Sub
        Range(.Rows(1), .Rows(6)).Locked = True
        Range(.Rows(7), .Rows(.Rows.Count)).Locked = False
        .Protect Password:="password", UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowDeletingRows:=True, AllowFiltering:=True
    End With
End Sub


Sub UnProtectMDS()
    With Worksheets("MDS Equipment Detail")
        If Not .ProtectContents Then Exit Sub
        .Unprotect Password:="password"
    End With
End Sub

[Paul_Hossler]

1. I put the protect subs on mod_Util since you have to run them manually and added the CA-CL write capability, marked with <<< below so that you can change it

Sub ProtectMDS()

    With Worksheets("MDS Equipment Detail")
        If .ProtectContents Then Exit Sub
        Range(.Rows(1), .Rows(6)).Locked = True
        Range("CA5").Resize(2, 16).Locked = False       '<<<<<<<<<<<<<<<<<<<
        Range(.Rows(7), .Rows(.Rows.Count)).Locked = False
        .Protect Password:="password", UserInterfaceOnly:=True, _
            DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, _
            AllowDeletingRows:=True, AllowFiltering:=True
        .EnableSelection = xlUnlockedCells
    End With
End Sub
 
 
Sub UnProtectMDS()
    With Worksheets("MDS Equipment Detail")
        If Not .ProtectContents Then Exit Sub
        .Unprotect Password:="password"
    End With
End Sub

2. You must have changed the layout of MOST so the header row was wrong

Public Const rowHeaderMOST As Long = 4
Public Const rowDataStartMOST As Long = 5

3. I changed the logic to not use the UsedRange on MDS but only down to the last row that has a Client

[pawcoyote]

Yes sorry I did add another Header in Column B.