Posts by zoog25

Thank you pike. The code is working perfectly now.

i get an error.

Run-time error '1004':

Select Method of Range class failed.

Hello all,

I'm having problems with the following code:

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Count > 1 Then Exit Sub


'Initiate Work
Dim lrow As Long
Dim NOCws As Worksheet
Dim TPws As Worksheet


Set NOCws = ThisWorkbook.Worksheets("NOC")
Set TPws = ThisWorkbook.Worksheets("Tract Parcels")


'Disable other sheet events
Application.EnableEvents = False
'Find last row in NOC Log
lrow = NOCws.Cells(Rows.Count, 5).End(xlUp).Row


Stop


'NOC Auto Entry After Map goes to Council
If Not Intersect(Target, Range("E:F")) Is Nothing Then
    If WorksheetFunction.CountA(Cells(Target.Row, "E").Resize(, 2)) = 2 Then
        MsgBox ("NOC Log Updating")
        With NOCws.Range("F4:F" & lrow)
            Set NOCTP = .Find(Cells(Target.Row, "G"), LookIn:=xlValues)
            Do
                If Not NOCTP Is Nothing Then
                    firstaddress = NOCTP.Address
                    If Cells(Target.Row, "H").Value = NOCws.Cells(NOCTP.Row, "G").Value Then
                        If Cells(Target.Row, "D").Value = NOCws.Cells(NOCTP.Row, "E").Value Then
                            MsgBox "NOC entry already made.", vbInformation, "ENTRY MADE"
                            GoTo DoneFinding
                        End If
                    Set NOCTP = .FindNext(NOCTP)
                    End If
                Else
                    lrow = lrow + 1
                    If MsgBox("Is this Private Site Project?", vbQuestion + vbYesNo, "Public or Private") = vbYes Then
                        NOCws.Cells(lrow, "A") = "PR"
                    Else
                        NOCws.Cells(lrow, "A") = "PUB"
                        [COLOR=#FF0000]NOCws.Range("W" & lrow & ":Y" & lrow).Select[/COLOR]
                        With Selection.Interior
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                            .ThemeColor = xlThemeColorLight1
                            .TintAndShade = 0.499984740745262
                            .PatternTintAndShade = 0
                        End With
                    End If
                    NOCws.Cells(lrow, "B") = Cells(Target.Row, "A").Value
                    NOCws.Cells(lrow, "C") = Cells(Target.Row, "B").Value
                    NOCws.Cells(lrow, "D") = Cells(Target.Row, "E").Value
                    NOCws.Cells(lrow, "E") = Cells(Target.Row, "D").Value
                    NOCws.Cells(lrow, "F") = Cells(Target.Row, "G").Value
                    If Cells(Target.Row, "H").Value = "" Then
                        NOCws.Range("G" & lrow).Select
                        With Selection.Interior
                        .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                            .ThemeColor = xlThemeColorLight1
                            .TintAndShade = 0.499984740745262
                            .PatternTintAndShade = 0
                        End With
                    Else
                        NOCws.Cells(lrow, "G") = Cells(Target.Row, "H").Value
                    End If
                    NOCws.Cells(lrow, "I") = Cells(Target.Row, "I").Value
                    MsgBox "Agreement has been added to NOC Log.", vbInformation, "ENTRY MADE"
                End If
            Loop While Not NOCTP Is Nothing
DoneFinding:
        End With
    End If
End If


Application.EnableEvents = True


End Sub

For whatever reason, it isn't liking the red section of my code. I'm trying to select columns W through Y of row "lrow" and adding color to those cells. I'm not sure if i'm just not coding right but all examples i have seen point to this method. Please let me know if there is a different method or if i'm just missing something to make my code work. Thank you.

Hello All,

I need help with coding for a worksheet change event.

The situation is i have two sheets. "Tract Parcels" and "NOC".
In "Tract Parcels" i have columns C, D, E, G, H, I with information. When all each column is filled, it would first check to see if B, D, E, G, H, & I information is located in columns C, E, D, F, G, I respectively. It it does then a message box appears saying "Project already appears in NOC Tracker.", otherwise the program will find the first empty row and copy and paste the information into that row. Don't know if this is too complicated, so please let me know if there needs to be more clarity.

Also, if say this worksheet change event runs and starts filling in "NOC". I have a change event in "NOC", could the change event in "NOC" be disabled when the first event runs.

Hello All,

It's been a long time since i have really done any coding and i'm having some issues. Currently i'm trying to write an worksheet event change macro. The situation is this, the worksheet has several columns, The key columns here is A and F. Now what i'm trying to do is that after a person inputs a value into column F and either clicks away, tabs over, or hits enter, it will check the corresponding column A cell as the entered information in F was. If A is blank then it pops up a question box. If A isn't blank then nothing happens.

Dim R As Range
Dim Cell As Range




Set R = Intersect(Target, Range("F:F"))


    If Not R Is Nothing Then
            For Each Cell In R
                If Cell.Value <> "" Then
                    If MsgBox("Is this a Public Tract/Parcel Project?", vbQuestion + vbYesNo, "Public or Private") = vbNo Then
                        Cells(Target.Row, "A").Value = "PR"
                    Else
                        Cells(Target.Row, "A").Value = "PUB"
                    End If
                End If
            Next Cell
    End If

Now I have been researching and trying my best to make it so the question only appears after a value is entered in to "F" and then it doesn't appear again, unless say the information from "F" is deleted. So basically if "F" is blank, it runs a question to fill in "A". Then as long as the value of "F" isn't deleted, then question doesn't come up again, unless the value of "F" is deleted and "F" is now blank.. Now note the value in "F" is going to always be a string of numbers like "6730". 4 to 5 digits in length.

Re: Converting a text string into a number string

Thanks M40wen, I changed my projindex as Long and now it works. Thanks.

Re: Converting a text string into a number string

yeah I have it dimmed as String.

I did that because the projnum is in the format "###-##-##" when I do the Left function.

Re: Converting a text string into a number string

Hello Krishnakumar

I tried adding your code to my project and I get an overflow error.

Here is how I incorporated it:

projnum = Left(jobarray(3), 9)
projindex = Format(Val(Replace(projnum, "-", vbNullString))*100+1, "###,###,###")

Don't know why it is giving the overflow error.

Hello all,

Here is the situation, I am working on a macro that extras folder names that are located in a directory. I've been able to isolate a unique text string that looks like this "987-65-43". Now I need to be able to convert that text into a single number with two additional digits. So macro would work to convert the text string "987-65-43" to the number 987,654,301. So in a sense it would remove the dashes and the number would then be multiplied by 100 to move the new number string over two spaces and then add 1 to the number in order to get 987,654,301. Please help me figure out this process.

Please note that the text string will always be in the format "###-##-##" and the new number would be put into a variable called proindex.

Thank you for any assistance you can provide.

Hello All,

Here is the situation and I'm not sure if this is possible. I'm working on a macro connected to a command button. Now when a sure clicks on the button. One of those Windows directory windows appears and prompts the user to select a folder. The folder path is then extracted and separated outs. The folder path will typically be in this format.

J:\Projects\### Text\###-##-## Text\Doc

I need the information located in "\### Text" and "\###-##-## Text".

Anyways if this is possible please let me know. I'll continue to do research to see if this is possible.

Re: Coding for creating hyperlink depending on worksheet change.

Thanks KjBox. The addition of the code work perfectly.

Re: Coding for creating hyperlink depending on worksheet change.

If you use do something like insert a row then the code runs or gives me an error

Hello all,

I'm dealing with coding that creates a hyperlink based on if you enter information into a column. The problem I'm having is that if any changes that touches the column triggers the coding. I was wondering if it is possible to modify the code only if a physical entry is made in the column that the code is initiated.

Anyways here is the code I'm using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sPath As String
Dim sDefaultPath As String
Dim fd As FileDialog
'Submittal Package Link
Dim R As Range
Dim cell As Range
    
Set R = Intersect(Target, Range("L:L"))
    
If Not R Is Nothing Then
    For Each cell In R
        If MsgBox("Do you wish to link to the submittal package?", vbQuestion + vbYesNo, "Link to File?") = vbNo Then
            Cells(Target.Row, "B").Value = "C"
            Exit Sub
        End If
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        sDefaultPath = "J:\Projects"
        With fd
            .AllowMultiSelect = False
            .InitialFileName = sDefaultPath
            .Title = "Select File to Link to"
            .ButtonName = "Select File"
            If .Show = True Then
                sPath = .SelectedItems(1)
            Else
                Target.Hyperlinks.Delete
                Exit Sub
            End If
        End With
        ActiveSheet.Hyperlinks.Add Anchor:=Target, Address:=sPath
        Cells(Target.Row, "B").Value = "C"
        MsgBox "Link successfully created to " & sPath, vbInformation, "Link Created"
    Next cell
End If
    
End Sub

Also if there is a better way of doing it, please let me know.

Re: userform combobox populated from two columns of a table

Hey all thanks for the help.

I was able to make a few adjustments and got the code userform to work how I wanted it too. This saved me a lot for the ultimate project I'm working on. I'm including the final code below:

Private Sub cmdbtOpen_Click()
Dim PSH As String
If cbExisting.Value <> "" Then
    PSH = Left(cbExisting.Value, 6)
    Sheets(PSH).Show
    Sheets(PSH).Select
End If
End Sub
Private Sub UserForm_Initialize()
     
    Set myTbl = Sheets("Client Records").ListObjects("ClientRec")
     
    LR = myTbl.Range.Rows.Count
     
    For i = 2 To LR
        If arStr = "" Then
            arStr = Sheets("Client Records").Cells(i, Range("ClientRec[Project ID]").Column) & " " & Sheets("Client Records").Cells(i, Range("ClientRec[Project Name]").Column)
        Else
            arStr = arStr & "/" & Sheets("Client Records").Cells(i, Range("ClientRec[Project ID]").Column) & " " & Sheets("Client Records").Cells(i, Range("ClientRec[Project Name]").Column)
        End If
         
    Next i
    
    ufExisting.cbExisting.List = Split(arStr, "/")
     
End Sub

Re: userform combobox populated from two columns of a table

Thanks royUK for the information. I will test your code once I get the first one from Adrian B's code.

Now the code for the combo box works except instead of show two columns, all I get is one column with first information and then second information below it. I'm attaching a sample of the workbook to make things easier. Please see what you guys can do to fix it.

Re: userform combobox populated from two columns of a table

Column 1 has a project code like "077-09", Column 6 has the project name. Now each sheet in the workbook is title by the job number so in this case "077-09". So it takes the number sequence of ###-## and then opens that worksheet.

Now I tried Adrian code and it works but instead of shows it like "077-09 Project Name" it goes

077-09
Project Name

so now I just need to have first the columns to combine in the list so it appears at 077-09 Project Name and then once selected the userform has a button that would essentially open the worksheet (Show, Unhide, Select, how ever that is called) and the user would see the project sheet.

Re: userform combobox populated from two columns of a table

I'm doing two columns in the combobox which would look like one.

Re: userform combobox populated from two columns of a table

Hello Adrian

I tried your code but I get an error message at set rng1. I don't know why it doesn't recognize it.

Hello all,

Here is my situation:

I have a userform called ufExisting. On the userform i have a combobox called cmbExisting.

Now here is what i'm trying to do.

On a sheet called "Client Records" i turned it into a table called [ClientRec].

Now i want to populate the combobox with columns tables 1 and 6.

The reason i'm doing this is cause the combobox selection will open a sheet in the workbook that is based on the information from the column 1 table.

Thank you for any assistance that you can provide.

Re: Coding Assistance With UserForm Using a Checkbox

Hey cytop. Thanks for the assistance. It works great. I made the change to the code like you suggested and now it works great. Here is the final code.

Private Sub cmdAddEntry_Click()




Dim ws As Worksheet
Set ws = Worksheets("Master")




With ws
    If cbRev.Enabled = True Then
        ActiveCell.Offset(0, 1).Value = txtJob.Value & " " & txtScope.Value & " " & "(Rev).pdf"
    Else
        ActiveCell.Offset(0, 1).Value = txtJob.Value & " " & txtScope.Value & ".pdf"
    End If
End With




'clear the data
Me.txtScope.Value = ""
Me.txtJob.Value = ""




End Sub

Thanks for the assistance.