Next Row VBA issue - Beginner

[dellehurley]

Hello,
I am really hoping someone came help me. I have been trying to teach my self VBA using YouTube etc as my teacher. I have working on this Data Form, I am getting there but I having one issue. I have tried heaps of things and this is the closest I have come to it working.

I have set up a user form. My issue is the record in green should be one line and the record in yellow another. #Value should be replaced with the File name.

I have gone around in circle but this trying to make it work the code I have now is.....

Dim msgValue As VbMsgBoxResult
Dim sh4 As Worksheet
Dim sh2 As Worksheet
Dim iRow As Long
iRow = [Counta(Database!A:A)] ' identifying the last row

Set sh4 = ThisWorkbook.Sheets("File Name Sheet")
Set sh2 = ThisWorkbook.Sheets("Database")

With sh4

.Range("J12").Value = Me.cmbFileNo.Value

.Range("K12").Value = Me.cmbType.Value

.Range("L12").Value = Me.cmbEvent.Value

.Range("M12").Value = Me.cmbExt.Value


If FrmForm.txtRowNumber.Value = "" Then

iRow = [Counta(Database!A:A)] + 1
Else

iRow = FrmForm.txtRowNumber.Value

End If

With sh2

.Cells(iRow, 1) = sh4.Range("I12").Value


If msgValue = vbNo Then Exit Sub

Call Submit
Call Reset

End With
End With


(I realise the last "end with" shouldn't be there but it does not work at all without it.) Any advise is appreciated. Dannielle

 

[dellehurley]

Thanks Dave, you have been a huge help.
Just so you know I haven't updated again as it is all working okay and why temp fate.

 

[dmt32]

Thanks Dave, you have been a huge help.
Just so you know I haven't updated again as it is all working okay and why temp fate.

happy to help but if find things do not work as intended then have updates to try

Good luck with project

Dave

 

[dellehurley]

Hi Dave,
I must apologise. I had a closer look at the vba you wrote and it is perfect ?

I have 1 small issues which I have no idea how to fix.


This code does not work, the error looked like this

'change folder path to database if required
    FolderName = ThisWorkbook.Path & "C:\Users\delle\OneDrive\Documents\Genealogy\!Family Tree Files and Photos!\"
    
    FileName = Me.lstDatabase.Value

So I tried this. It tried to open the files in Excel.

'change folder path to database if required
    FolderName = "C:\Users\delle\OneDrive\Documents\Genealogy\!Family Tree Files and Photos!\"
    
    FileName = Me.lstDatabase.Value

How do I make the following columns not visible on the Userform. (ie. File Type and Ext.)?

Thanks

 

[dellehurley]

I forgot to add the link to the file, here it is.

Database Link

 

[dmt32]

Hi Dave,
I must apologise. I had a closer look at the vba you wrote and it is perfect ?

I have 1 small issues which I have no idea how to fix.
How do I make the following columns not visible on the Userform. (ie. File Type and Ext.)?

Thanks

In your Reset Code towards to end of it you have a line that sets Listbox Column Widths - set the columns you do not want visible to zero

replace it with this line

.lstDatabase.ColumnWidths = "65,40,0,50,0,150,40,150"

and hopefully, will do what you want

Dave

 

[dellehurley]

The columns a perfectly hidden now, thank you.

I just re-read my previous post and some info is missing, it doesn't make much sense, sorry about that.

The code finds the file but it opens it in excel and needless to say does not work, it looks like gibberish. Any advice?

Dannielle

 

[dmt32]

The columns a perfectly hidden now, thank you.

I just re-read my previous post and some info is missing, it doesn't make much sense, sorry about that.

The code finds the file but it opens it in excel and needless to say does not work, it looks like gibberish. Any advice?

Dannielle

Hi,
sorry, did not think just assumed you were opening workbooks

Try this update & see if helps you

Private Sub lstDatabase_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    Dim FolderName As String, FileName As String
    Dim myShell As Object
    
'change folder path to database if required
    FolderName = "C:\Users\delle\OneDrive\Documents\Genealogy\!Family Tree Files and Photos!\"
    
    FileName = Me.lstDatabase.Value

    On Error GoTo myerror
    
    If Not Dir(FolderName & FileName, vbDirectory) = vbNullString Then
        Set myShell = CreateObject("WScript.Shell")
        myShell.Run FolderName & FileName
    Else
        Err.Raise 53
    End If

'report errors
myerror:
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
    Set myShell = Nothing
End Sub

Dave

 

[dellehurley]

Hi Dave,
Sorry I have only just managed to try the above fix it comes up with
Method 'Run' of object 'IWshShell3' failed
I googled it but honestly this is way above my pay grade
Dannielle

 

[dmt32]

Hi Dave,
Sorry I have only just managed to try the above fix it comes up with
Method 'Run' of object 'IWshShell3' failed
I googled it but honestly this is way above my pay grade
Dannielle

Might be above mine as well but suspect could be something in the FolderPath causing the error

Can you place lates copy of your workbook in your dropbox also, what version of windows are you running?

Dave

 

[dellehurley]

Hi Dave,
This is a link to the folder which contains the database file and a few of the documents which go with the database. The information in the correct information, the database corresponds with the files in the folder. Don't worry if you change anything as it is only a copy. I tried to do this through Onedrive but it will not let me share a macros enable file for some reason, so I moved everything to Dropbox.
BTW -The path did work when it thought it was opening an excel workbook, the issue was only that it used excel not a compatible program for jpg's and pdf's.

Dropbox link

I have changed the path to "C:\Users\delle\Dropbox\!Family Tree Files and Photos!\"
OS Name Microsoft Windows 10 Home Version 10.0.18362 Build 18362
Excel is 365 office subscription.

Dannielle