Posts by stildawn

Hi All

I think this will be a simple one, basically I have a code that checks some stuff, and I would like it to stop when it finds a problem with the data and select the cell so the user can do stuff.

Range.Select works, the cell is selected, however if its way down the rows it wont show, the user still has to scroll down and find the selected cell, is there another command to add in so the screen also goes to the cell so that its immediately visible to the human eye?

Thanks in advance.

Re: Using .Find but with multiple possible search strings?

Trying this now, thanks. Hopefully it works and I can just keep adding potential headers to make the code more robust over time.

Re: Messaging all users currently using Shared Workbook

Buid a proper DB with like Access or something. Shared workbooks are a nightmare, I have tried and tried numerous times, the end result is always its not a good idea haha.

Hi All

I have an invoice which I format using VBA, in this I have a .Find code that finds the header line so it can delete all the other crap.

The issue I have is that the people who provide this for me keep changing the working in the header, so I was thinking, is there away to have a .Find code but have it check through mulitple possible strings?

So something like this:

.Find("FOB PRICE") or .Find("UNIT PRICE") or .Find("PRS") or .Find("TTL") etc etc

So it goes through this list of key words looking for each one in turn? If it finds one then it stops there?

Thanks in advance

Re: Delete selection based on user selection

Um working with this bit as I think this is what your asking:

If Clear = vbYes Then
             
            Call Save_Report
        Else
             
        End With
        AutoFilterMode = False
        Application.ScreenUpdating = True
        Set lastrow = Nothing
        Set dStartDate = Nothing
        Set dEndDate = Nothing
        Set bflag = Nothing
        Set Clear = Nothing

Change to this I think:

If Clear = vbYes Then
            Call Save_Report
            .Range("A2:I" & Lastrow).SpecialCells(12).ClearContents
            AutoFilterMode = False
            Application.ScreenUpdating = True
            Set Lastrow = Nothing
            Set dStartDate = Nothing
            Set dEndDate = Nothing
            Set bflag = Nothing
            Set Clear = Nothing
        Else
            AutoFilterMode = False
            Application.ScreenUpdating = True
            Set Lastrow = Nothing
            Set dStartDate = Nothing
            Set dEndDate = Nothing
            Set bflag = Nothing
            Set Clear = Nothing
        End If
    End With

Save a copy and test first cause this is a best guess from what you've told us.

Re: Userform to select sheets in VBA

Your right in the use of a userform, just go ahead and create the userform with three check boxes and a continue button.

Then code something like this in your current code:

Public SheetNum As Long

Put this at the top to declare a public variable

Userform1.Show

Put this where you would like the user to make the sheet selection.

In the Userform in the "Click" event code for the Continue Button put something like:

If CheckBox1.Value = True  Then
  SheetNum = 1
Else
  If CheckBox2.Value = True Then
    SheetNum = 2
  Else
    If CheckBox3.Value = True Then
      SheetNum = 3
    Else
    End If
  End If
End If


Unload Me

This will then assign the checkedsheet number to the SheetNum variable, so you can use that later on it your code, like for example:

Thisworkbook.Sheets(SheetNum).Range("A1") = "Hello"

For example.

Hope this helps.

Re: RunTime 91 Error Using .Find

Grrr dont worry, its cause the cells in the range were formula's, I did a copy pastespecial values and now it works.

Hi All

I am having issue with a 91 error, I have tried all the different versions I can think of but nothings working and I dont know why.

Here is the full code of the userform:

Public GCol As Long
Public MapStart As String








Private Sub GameType_Change()




Map.Clear




GCol = ThisWorkbook.Sheets(1).UsedRange.Find(GameType.Value & " LIST").Column
Lastrow = ThisWorkbook.Sheets(1).Cells(Rows.Count, GCol).End(xlUp).Row




For i = 2 To Lastrow
    Map.AddItem ThisWorkbook.Sheets(1).Cells(i, GCol).Value
Next i




DesRow = ThisWorkbook.Sheets(2).UsedRange.Find(GameType.Value).Row
TypeDes.Caption = ThisWorkbook.Sheets(2).Range("B" & DesRow).Value




End Sub




Private Sub Map_Change()




Found = ThisWorkbook.Sheets(1).Range(Cells(1, GCol), Cells(50, GCol)).Find(Map.Value)




MRow = Found.Row




MsgBox MRow




End Sub




Private Sub UserForm_Initialize()




GameType.AddItem "CAPTURE THE FLAG"
GameType.AddItem "DUEL"
GameType.AddItem "FREE FOR ALL"
GameType.AddItem "KING OF THE HILL"
GameType.AddItem "LAST TEAM STANDING"
GameType.AddItem "TEAM DEATHMATCH"
GameType.AddItem "TEAM OBJECTIVE"




End Sub

My issue currently is with the procedure Map_Change

Private Sub Map_Change()




Found = ThisWorkbook.Sheets(1).Range(Cells(1, GCol), Cells(50, GCol)).Find(Map.Value)




MRow = Found.Row




MsgBox MRow




End Sub

All I'm trying to get is the row number... But whatever I do it errors out, I think its something to do with the range part for find to search in.

Any ideas?

Re: I'm not closing off Excel properly I think?

Yep cause in Access (and I believe any other program where you are working with automation with Excel) if you make a reference to an Excel element that isn't properly referenced back to the Excel object you created, Access will "start" a new hidden instance of Excel, which since its new and separate to your Excel object, its not closed off properly.

This instance will be kept "running" as long as that instance of Access is open, it will close if you close Access, hence why second runs of the same code cause an issue, but first runs work.

Re: I'm not closing off Excel properly I think?

Oh wow.... That makes perfect sense damn it hahaha I was looking over and over it for the missing reference to the objExcel object, totally glossed over that.

Let me test now.

Excellent thanks a bunch for spotting that. That was the issue its all working now and closing off Excel in task manager also.

Re: I'm not closing off Excel properly I think?

Any ideas on this? I know its an easy fix just don't know it personally?

Hi All

Doing some VBA in Access and I have this code which basically opens and formats a Excel sheet then saves it ready for import:

Sub FormattExcel()


Set f = Application.FileDialog(1)
With f
    .AllowMultiSelect = False
    .Show
    For i = 1 To .SelectedItems.Count
        filepath = .SelectedItems(i)
    Next i
End With












Set objExcel = CreateObject("Excel.Application")




With objExcel
    .Workbooks.Open filepath
    file = Right(filepath, Len(filepath) - InStrRev(filepath, "\"))
    .Workbooks(file).Sheets(1).Range("G:H,K:L").Delete
    Lastrow = .Workbooks(file).Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    .Workbooks(file).Sheets(1).Range("F2:F" & Lastrow).NumberFormat = "dd/mm/yyyy"
    For Each rgCell In .Workbooks(file).Sheets(1).Range("F2:F" & Lastrow).Cells
        DateSplit = Split(Left(rgCell, 10), ".", 3)
        rgCell.Value = DateSerial(DateSplit(2), DateSplit(1), DateSplit(0))
    Next rgCell
    
    .Application.DisplayAlerts = False
    .Workbooks(file).SaveAs "C:\Users\tbaker\Desktop\Merge\FB DB Saves\ImportTemp.xls", 56
    .Workbooks("ImportTemp.xls").Close False
    .Quit
End With




Set objExcel = Nothing




End Sub

However I notice that Excel is still running in task manager after using this code, and if I run it again it will crash out, but if I end process in task manager, then run it, its fine.

So I think I'm not ending excel in the code properly?

Any ideas?

Thanks

Re: Updating the Access Table with a excel spread sheet

In an update to this, ideally each time I do this update I would like it to also use the following code:

Function LogChanges(lngID As Long, Optional strField As String = "")    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim varOld As Variant
    Dim varNew As Variant
    Dim strFormName As String
    Dim strControlName As String
    
    varOld = Screen.ActiveControl.OldValue
    varNew = Screen.ActiveControl.Value
    strFormName = Screen.ActiveForm.Name
    strControlName = Screen.ActiveControl.Name
    Set dbs = CurrentDb()
    Set rst = dbs.TableDefs("ztblDataChanges").OpenRecordset
    
    With rst
        .AddNew
        !FormName = strFormName
        !ControlName = strControlName
        If strField = "" Then
            !FieldName = strControlName
        Else
            !FieldName = strField
        End If
        !RecordID = lngID
        !UserName = Environ("username")
        If Not IsNull(varOld) Then
            !OldValue = CStr(varOld)
        End If
        !NewValue = CStr(varNew)
        .Update
    End With
    'clean up
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
End Function

This code works currently on forms, so that when you update a field it records each change in a separate "DataChanges" table.

Ideally the daily updates from Excel would also populate the "DataChanges" table using this code? So perhaps is there away to do it through a form? Or code using this code?

Thanks, Access is not my strong suit.

Hi All

I am just starting out on a new Access project.

Basically what I have in Access is a table which lists jobs via their HB Number (as well as a ID number access gives them). For each job there is a bunch of details and some Yes/No fields.

Each day I will get a dump from another system that will list jobs like this, I then need to "Update" the Access table with any new information from the excel dump. The update would need to:

1. Insert new records (jobs) from Excel dump into Access Table
2. Update any of the records fields (except HB of course as its unique) in the access table from the Excel dump (the Excel dump obviously wont have access ID numbers, but will have the HB)

For a real basic example:

Access Table Like So:

[TABLE="width: 500"]

ID

HB

Finished

1

5A

Yes

2

5B

No

3

5C

No

4

5D

No

[/TABLE]

Excel Dump looks like this:

[TABLE="width: 500"]

HB

Finished

5A

No

5B

Yes

5C

Yes

5D

Yes

5E

No

[/TABLE]

So some Yes/No's are different and there is a new HB called "5E"

After the "update" Access should look like this:

[TABLE="width: 500"]

ID

HB

Finished

1

5A

No

2

5B

Yes

3

5C

Yes

4

5D

Yes

5

5E

No

[/TABLE]

This example is overly simple, the actually data has lots of different fields and many many many more records. But yeah basically need to update the table from an excel dump.

Any ideas on how to do this? I thought it was as simple as doing a Excel Import > Append Table thing in Access, but that just seems to add the new records and ignore the updated fields?

Thanks in advance

Cheers

Also posted here: http://www.mrexcel.com/forum/m…ad-sheet.html#post3790734

Re: Find empty cells in column and auto input formula with certain criteria using VBA

Like this:

Private Sub CommandButton3_Click()     
     
    Dim wks As Worksheet
    Dim LastRow As Long
     
    Set wks = ActiveSheet
    With wks
        Found = 0
        LastRow = .Range("M" & Rows.Count).End(xlUp).Row
        For i = 1 To LastRow
            If .Range("M" & i) = "" Then
                If .Range("E" & i) = "" Then
                Else
                    .Range("M" & i) = "=TODAY()"
                    Found = Found + 1
                End If
            Else
            End If
        Next i
         
         
        If Found = 0 Then
            MsgBox "No blanks found"
            Exit Sub
        Else
            MsgBox Found & " blank cells located and ""=TODAY()"" entered"
        End If
         
    End With
End Sub

Re: Find empty cells in column and auto input formula with certain criteria using VBA

I could do this with a standard loop, I don't know if its possible using the specialcells method though.

Re: Clearing contents of specific cells on different sheets within the same Workbook

You should go through and remove all the .select lines and just merge the first parts of the .select lines with the last part of the following Selection. lines

For example:

Yours:

Sheets("VO").Select
Range("B19:D27").Select
Selection.ClearContents

Is better as:

Sheets("VO").Range("B19:D27").ClearContents

If its stopping on Sheets("Cutting").Select, the sheet of that name either doesnt exist or is hidden I believe

Re: Creating folder beside the excel file

It is a highly useful language to know, especially in any business/office type work position. I use it multiple times daily to automate just about everything I do. Its fairly easy to get the hang of also.

Re: Deleting rows clears range variable

Yeah get rid of the declaration lol. Or just change it to String. OriginalRange is no longer a range but simply a String representing a range.

Re: Simple Inventory Template with Order Form

What you describe doesn't sound that easy to setup, especially with little experience. Also depending on the size of your inventory etc, it might be a better idea to use a database like Access.