Posts by daren.beaney

Re: UserForm - Updating active record from user form.

This is amazing! Thanks for your expert help on this. It makes total sense to do it this way rather than the over complicated and limited bound controls. I've renamed the controls as you mentioned, which does make it easier to identify what they relate to. I can manage with the limitation of 10 columns.

Thanks again for all your help!

Re: UserForm - Updating active record from user form.

Well this is what I have however after it goes through the first .Range... it jumps to refreshing the form which to me seems to be related to the lstMyData (second piece of code).

Private Sub cmdUpdate_Click()
     
    Dim r As Excel.Range
     
    Set r = Worksheets("ES").Range("A:A").Find(What:=lstMyData.List(lstMyData.ListIndex, 0), lookat:=xlWhole)
     
    If Not r Is Nothing Then
         
        With Worksheets("ES")
            .Range("AB" & r.Row).Value = Control8.Value 'Code jumps from here to refresh the page and again after each line below.
            .Range("AI" & r.Row).Value = Control9.Value
            .Range("AJ" & r.Row).Value = Control10.Value
            .Range("AK" & r.Row).Value = Control11.Value
            .Range("AL" & r.Row).Value = Control12.Value
            .Range("AM" & r.Row).Value = Control13.Value
            .Range("AO" & r.Row).Value = Control14.Value
            .Range("AP" & r.Row).Value = Control15.Value
        End With
      End If
    Set r = Nothing
         
    End Sub

The code it bounces to to reload the form:

Sub lstMyData_Click()
'dim the variables
Dim i As Integer




'find the selected list item
i = Me.lstMyData.ListIndex
Me.lstMyData.Selected(i) = True




'add the values to the text boxes
Me.Control1.Value = Me.lstMyData.Column(0, i)
Me.Control2.Value = Me.lstMyData.Column(28, i)
Me.Control3.Value = Me.lstMyData.Column(31, i)
Me.Control4.Value = Me.lstMyData.Column(32, i)
Me.Control5.Value = Me.lstMyData.Column(33, i)
Me.Control6.Value = Me.lstMyData.Column(1, i)
Me.Control7.Value = Me.lstMyData.Column(2, i)
Me.Control8.Value = Me.lstMyData.Column(27, i)
Me.Control9.Value = Me.lstMyData.Column(29, i)
Me.Control10.Value = Me.lstMyData.Column(34, i)
Me.Control11.Value = Me.lstMyData.Column(35, i)
Me.Control12.Value = Me.lstMyData.Column(36, i)
Me.Control13.Value = Me.lstMyData.Column(37, i)
Me.Control14.Value = Me.lstMyData.Column(38, i)
Me.Control15.Value = Me.lstMyData.Column(39, i)




End Sub

The final result overwrites the changes made in the form with data in the worksheet.

Re: UserForm - Updating active record from user form.

Hi sorry, I responded without testing it properly the code seems to jump once it completes the first .Range... line. any ideas how to update the cells as per the code in the attached.

Re: UserForm - Updating active record from user form.

Thanks for this quick response, that worked a treat with a small tweak.

Private Sub cmdUpdate_Click()
     
    Dim r As Excel.Range
     
    Set r = Worksheets("ES").Range("A:A").Find(What:=lstMyData.List(lstMyData.ListIndex, 0), lookat:=xlWhole)
     
    If Not r Is Nothing Then
         
        With Worksheets("ES")
            .Range("AD" & r.Row).Value = Control9.Value 'AD being the related column that needs to be updated with the value
            ...
            ...
        End With
      End If
    Set r = Nothing
         
    End Sub

Hi, I'm pretty new to forms and I've been struggling with trying to get a user form to update the active record it's displaying. The form includes a listbox displaying the list of records within the sheet and the form fields display the data relating to the selected record in the listbox.

Unfortunately I can't workout how to use an update button to update the cells regardless whether changes were made or not to the content because it's a dynamic list in the form. I've attached my sample workbook.

Any help on this will be massively appreciated.

Re: Searching cells for keyword and moving row to new sheet

Hi Mumps, I added an additional line to select the existing sheet if it already exists.

Option Compare Text
Sub Copydata()
    Application.ScreenUpdating = False
    Dim bottomG1 As Integer
    bottomG1 = Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row
    Dim bottomG2 As Integer
    Dim rng1 As Range
    Dim rng2 As Range
    Dim foundVal As String
    Dim ws As Worksheet
    Set ws = Nothing
    foundVal = InputBox("Please enter the word to find.")
    For Each rng1 In Sheets("Sheet1").Range("G2:G" & bottomG1)
        If rng1 Like "*" & foundVal & "*" Then
            On Error Resume Next
            Set ws = Worksheets(foundVal)
            On Error GoTo 0
            If ws Is Nothing Then
                Worksheets.Add(After:=Sheets(Sheets.Count)).Name = foundVal
            End If
            ws.Select
            rng1.EntireRow.Copy ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next rng1
    bottomG2 = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row
    For Each rng2 In ActiveSheet.Range("G2:G" & bottomG2)
        If Not rng2 Like "*" & rng2.Offset(0, -1) & "*" Then
            rng2.Interior.ColorIndex = 6
            rng2.Offset(0, -1).Interior.ColorIndex = 6
        End If
    Next rng2
    Application.ScreenUpdating = True
End Sub

Thanks for your help with this, I'm a very happy man...

Re: Searching cells for keyword and moving row to new sheet

Quote from Mumps;682432

Try:

Code

Option Compare Text
Sub Copydata()
    Application.ScreenUpdating = False
    Dim bottomG1 As Integer
    bottomG1 = Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row
    Dim bottomG2 As Integer
    Dim rng1 As Range
    Dim rng2 As Range
    Dim foundVal As String
    Dim ws As Worksheet
    Set ws = Nothing
    foundVal = InputBox("Please enter the word to find.")
    For Each rng1 In Sheets("Sheet1").Range("G2:G" & bottomG1)
        If rng1 Like "*" & foundVal & "*" Then
            On Error Resume Next
            Set ws = Worksheets(foundVal)
            On Error GoTo 0
            If ws Is Nothing Then
                Worksheets.Add(After:=Sheets(Sheets.Count)).Name = foundVal
            End If
            rng1.EntireRow.Copy ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next rng1
    bottomG2 = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row
    For Each rng2 In ActiveSheet.Range("G2:G" & bottomG2)
        If Not rng2 Like "*" & rng2.Offset(0, -1) & "*" Then
            rng2.Interior.ColorIndex = 6
            rng2.Offset(0, -1).Interior.ColorIndex = 6
        End If
    Next rng2
    Application.ScreenUpdating = True
End Sub

Display More

Thanks Mumps, that works great. The idea of not creating a new sheet if it exists is so that if i use the same workbook with new data, it can add new rows to the existing sheet if i use the same foundval. How do I get it to carry out that task. Currently if I repeat the process and use the same foundval, it just highlights the columns w and v on sheet1.

Re: Searching cells for keyword and moving row to new sheet

Quote from Mumps;682314

Hi Daren. Try:

Code

Option Compare Text
Sub Copydata()
    Application.ScreenUpdating = False
    Dim bottomG1 As Integer
    bottomG1 = Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row
    Dim bottomG2 As Integer
    Dim rng1 As Range
    Dim rng2 As Range
    Dim foundVal As String
    foundVal = InputBox("Please enter the word to find.")
    For Each rng1 In Sheets("Sheet1").Range("G2:G" & bottomG1)
        If rng1 Like "*" & foundVal & "*" Then
            rng1.EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next rng1
    bottomG2 = Sheets("Sheet2").Range("G" & Rows.Count).End(xlUp).Row
    For Each rng2 In Sheets("Sheet2").Range("G2:G" & bottomG2)
        If Not rng2 Like "*" & rng2.Offset(0, -1) & "*" Then
           rng2.Interior.ColorIndex = 6
           rng2.Offset(0, -1).Interior.ColorIndex = 6
        End If
    Next rng2
    Application.ScreenUpdating = True
End Sub

Display More

Thanks that works really well and makes sense. What if I want to create a sheet with the foundval name? But only create the sheet it if it exists already otherwise update the existing sheet?

Hi,I'm trying to workout how to do the following:1. search the activesheet column G for a keyword that i enter into an input box. 2. when a cell is found containinig that word i then want to select the row and move a copyt into a new sheet.3. repeat the process until all rows have been searched and a new sheet contains all those that matched4. on the new sheet compare the cell in the column containing the keyword and the cell to the left, if they don't both contain the keyword then highlight both cells in yellow.I've attached an example of what the initial sheet would look like and have struggled to find anything close to what i want to do.Thanks in advance for any help you can provide.

Re: Update multiple worbooks from 1 workbook

Hi Smallman,I did say that this was working however I cant get it to work...I've checked the sheet names and file names, even created files with the exact sheet name.Can you see any issues in my code below?Thanks,Daren

Dim sPath As String    Dim sFil As String    Dim strName As String    Dim oWbK As Workbook    Dim sh As Worksheet    Dim ws As Worksheet    Dim Temp As String    Dim shNm As String    Temp = InputBox("Enter Sheet Name")    sPath = "G:\Master Files for HRBP" 'Change path to suit        For Each ws In ThisWorkbook.Worksheets        shNm = ws.Name        sFil = Dir(sPath & "*" & shNm & "*.xls")                Do While sFil  ""            strName = sPath & sFil            Set oWbK = Workbooks.Open(strName)            sFil = Dir            Set sh = Worksheets.Add            sh.Name = Temp            sh.Move After:=Sheets(Worksheets.Count) 'Pastes to end            ws.Range("F1").CurrentRegion.Copy            sh.Range("A1").PasteSpecial xlPasteValues 'Do you want the vals pasted?            oWbK.Close True        Loop    Next

Re: Update multiple worbooks from 1 workbook

Smallman,Thanks for the reply, it was down to my network path, for some reason it doesn't like the path. I tried it locally and it worked a treat!Thanks for your help on this!

Re: Update multiple worbooks from 1 workbook

Hi Smallman,Thanks for the reply!I tried the code and it seems to work fine to a point and reason being is my file names have other text i.e. 2011-2012 DPM CDG v12.xls it doesn't seem to find the file containing CDG. Is there anyway it can search for the file that contains the sheet name in the string?Really appreciate this!Daren

Hi,

I have a master workbook called "APR Compliance Master.xls" and in this workbook I have 27 sheets with a name such as CDG. I also have 27 workbooks in a folder location on our network for example called f:\Test update files.

What I've been asked to do is see whether it's possible to:

• use an input box to define the new sheet name for each workbook (which will be the same name for all workbooks eg "Sept")
• go through each sheet in my master file, copy the content in the currentregion and then...
• go to the folder location above and find the file that contains the master workbook activesheet name such as CDG (the file also has additional text such as version)
• create a new sheet at the end and use the name from step 1
• paste in the data copied and save and close the workbook
• go back to step 2

Hope that makes sense. I've been searching for weeks on this but can only find ways to merge content from multiple workbooks into a single sheet.

Kind regards,
Daren

Re: Find multiple values then delete cell and the next cell to the right

Thanks for you help on this it worked a treat!

Re: Find multiple values then delete cell and the next cell to the right

Thanks,I tried the code and I get an 'End Select' error as if it can't see the Select Case.

Re: Find multiple values then delete cell and the next cell to the right

Thanks for the reply!What if i need it to start in cell S6?

Re: Find multiple values then delete cell and the next cell to the right

Hi,I tried your code and the next issue is that it deletes any cells that contain the criteria. How do I change the code so that it only selects the cells that match the criteria?

Re: For Each Worksheet in... not moving to next worksheet.

Thanks rory! It works a treat.

Re: For Each Worksheet in... not moving to next worksheet.

Sorry about the format of my code and text, it's to do with the restrictions of my broswer and where i work.

Re: For Each Worksheet in... not moving to next worksheet.

Hi,thanks for the quick reply!! I've used rory's code and it works well except it overrights the header in column 'H'.The code starts off in Index, using the data from within Master and Data sheets and creates new sheets from the information contained within them.also the sheet names are in mixed case i.e. 'Master' but rory's code works fine, can anyone explain why?ThanksDaren