Posts by norie

Does this work?

For Each form In UserForms
    If Not form Is LandingPage Then
        Unload form
    End If
Next form

I mean the general structure of the data.

Do you need to have a layout where each item has it's own 'section'?

It's not impossible, it's just kind of difficult due to the way you have things laid out.

Why not have a separate sheet for each item?

That's kind of what my code but it does the row insertion before the rows are fully filled.

The reason for that is it's hard to determine when the rows are fully filled.

Let's say for FR-100 all 7 rows between BRAND and TOTAL are filled.

What should happen if the user tries to enter data for FR-100?

Should they be told there's not more room? Should the data go somewhere else?

What do you want to happen when there's no more room to add an entry?

Have a look at the attached workbook.

Are you opening the other workbooks in another instance of Excel?

Give this a try, it works with the uploaded workbook.

Option Explicit


Sub FillWorkOrderNos()
Dim rngWOs As Range
Dim rngWO As Range


    Set rngWOs = Sheets("Parts List").Columns(1).SpecialCells(XlSpecialCellsValue.xlTextValues)


    For Each rngWO In rngWOs.Cells
        If rngWO.Value <> "Work Order" Then
            rngWO.Copy rngWO.CurrentRegion.Columns(1)
        End If
    Next rngWO


End Sub

Can you upload a workbook with some sample data?

No problem!:)

Changing the format isn't the problem, it's the value that's the problem.

When I opened the workbook the dates aren't being recognised as dates, I assume it's the same for your colleague.

I converted them to 'real' dates by selecting column F, going to Date>Text to columns... and selecting MDY for the Column data format on the 3rd step.

As for preceding the format with a * - the dates in your workbook aren't formatted like that.

How are you getting the data from LinkedIn et al?

Try submitting the form.

IE.document.forms(0).submit()

I also get errors on the summary sheet and the problem is the dates in column F being in MDY format, i.e. US format and not being recognised as dates, in Europe anyway.

If I convert the dates using Data>Text to columns with the Column data format MDY on the third step then the formulas work.

Your colleague should be able to do the same.

Mind you, I was under the impression that provided the dates were valid the format wouldn't matter and would actually change based on locale, i.e. if the workbook was opened in the US the dates would appear in MDY format and if opened in Europe they would be in DMY.

NBVC

Thanks.

I saw that but didn't click it.

I might be being thick but I can't seem to find a way to follow a thread.

Try this.

Dim arrIn As Variant
Dim idxRow As Long


    With Sheets("Sheet1").CurrentRegion.Columns(4).Resize(,2)


         arrIn = .Value


         For idxRow = LBound(arrIn,1) To UBound(arrIn,1)
             If arrIn(idxRow, 2) = "Implement" Then
                 arrIn(idxRow,1) = "Flagged"
             End If
        Next idxRow


        .Value = arrIn


    End With

Try this.

Option Explicit


Sub AddPicture()
Dim ws As Worksheet
Dim picname As String


    For Each ws In ActiveWorkbook.Sheets


        picname = "G:\BUYING\Non Food\EK-VK Kalkulation\2019\6\SO" & ws.Range("B4") & "_VTF.jpg"    'Link to the picture


        ws.Pictures.Insert picname


        With ws.Pictures(ws.Pictures.Count)
            .Left = ws.Range("A1").Left
            .Top = ws.Range("A1").Top
            .ShapeRange.LockAspectRatio = msoFalse
            .ShapeRange.Height = 500#
            .ShapeRange.Width = 750#
            .ShapeRange.Rotation = 0#
        End With
    
    Next ws
    
    Application.ScreenUpdating = True
    
    Exit Sub
    
ErrNoPhoto:
    MsgBox "Unable to Find Photo"    'Shows message box if picture not found
    Exit Sub
    Range("P20").Select


End Sub

Mark

You could use DoCmd.OpenQuery for that, something like this

DIm strQueryName As String


    strQueryName = Me![combo_rule].Value


    DoCmd.OpenQuery strQueryName, acViewNormal

Note, you can change the 2nd argument View how you want the query displayed/what you want to do with it, see here for more information.