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

 

[dmt32]

Hi,
sorry for slow reply but have a major family event going on at moment so have had no time to play with this.

Code is working for me but guessing your issue may be the file path.
As an idea, can you try changing path to something a liitle simpler like "C:\Users\delle\Documents\" place a file there & see if that works?

If still having issues., suggest post code shown in #17 as a new thread & explain to forum error you are getting - hopefully, someone will be able to offer some guidance.

Dave

 

[dellehurley]

Hi,
sorry for slow reply but have a major family event going on at moment so have had no time to play with this.

Code is working for me but guessing your issue may be the file path.
As an idea, can you try changing path to something a liitle simpler like "C:\Users\delle\Documents\" place a file there & see if that works?

If still having issues., suggest post code shown in #17 as a new thread & explain to forum error you are getting - hopefully, someone will be able to offer some guidance.

Dave

? ? ? ? ?

I finally got it to work this is the final code I used. A mixture of the 2 you gave me.
The path had to be correct because I used the copy path function in file explorer to make sure it was correct however I probably put a " or \ in the wrong place.
Thank you for your help, I could not have done this without your help.
Dannielle

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 = ThisWorkbook.Path & "\"
    
    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

 

[dmt32]

? ? ? ? ?

I finally got it to work this is the final code I used. A mixture of the 2 you gave me.
The path had to be correct because I used the copy path function in file explorer to make sure it was correct however I probably put a " or \ in the wrong place.
Thank you for your help, I could not have done this without your help.
Dannielle

hi,
happy to have been of help & glad resolved

many thanks for feedback

Dave