Working between 2 worksheets - the 1st worksheet has the userform - the 2nd should contain the data

[smiley]

I'm new to this forum. I have a Workbook named Call Tracker which is a macro-enabled file with a userform in it. I also have another Workbook named Call Spreadsheet which is to contain the data captured from the userform. On the userform there's a SAVE button which should be opening the Call Spreadsheet file, going to the bottom row, and storing the information. My subroutine is in its infancy at this point, so I'm trying to test a number of things. I tried using the LastRow routine i found on the Internet, but it doesn't seem to be giving me the correct answer (although it might be that it's looking in the wrong place). So I wrote my own FOR ... NEXT loop to go through the Call Spreadsheet to identify the first non-blank row, but that's not working properly either.

Below is a sample of what my Call Spreadsheet looks like (Sheet1)



Below is what the Call Tracker spreadsheet looks like (Sheet1).



Following is the guilty routine....

Private Sub CommandButton1_Click()
    Dim wb1, wb2 As Workbook
    Dim ws1, ws2 As Worksheet
    Dim cb1, cb2, cb3, cb4 As String
    Dim NewRow, LastRow, i As Long
    Set wb1 = ThisWorkbook  'Store currently active workbook name.
    Set ws1 = wb1.Worksheets("Sheet1")
    cb1 = Me.ComboBox1.Value: cb2 = Me.ComboBox2.Value: cb3 = Me.ComboBox3.Value: cb4 = Me.ComboBox4.Value  'Capture information selected.
MsgBox ActiveWorkbook.Name
    'Reset ComboBox values.
    Me.ComboBox1.ListIndex = 1  'Set default value back to "No".
    Me.ComboBox2.ListIndex = 1  'Set default value back to "Customer".
    Me.ComboBox3.ListIndex = -1  'Set default value back to blank entry.
    Me.ComboBox4.ListIndex = -1  'Set default value back to blank entry.
    Me.ComboBox4.Visible = False

'If cb4 = "" Then
'End If

    Workbooks.Open "H:\Call Tracking\Call Spreadsheet.xlsx"
    Set wb2 = ThisWorkbook
    Set ws2 = wb2.Worksheets("Sheet1")
MsgBox ActiveWorkbook.Name

    With ws2
'        LastRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row
'MsgBox LastRow
        For i = 1 To 10000
            If Not IsEmpty(.Cells(i, 1)) Then  'If the cell isn't empty...
                i = i + 1
MsgBox .Cells(i, 1)
            Else
                NewRow = i
                Exit For
            End If
        Next
MsgBox NewRow

MsgBox [a1]
    End With
    
'MsgBox ComboBox1.Value & vbCrLf & ComboBox2.Value & vbCrLf & ComboBox3.Value
'MsgBox Environ$("USERNAME") & vbCrLf & Date$ & vbCrLf & Time$
End Sub

Both MSGBOXes "ActiveWorkbook.Name" show the correct workbook name.

The MSGBOX "LastRow" is commented out.

The MSGBOXes ".Cells(i, 1)" and "NewRow" show values that correspond to the Call Tracking spreadsheet instead of the Call Spreadsheet itself.

The MSGBOX [a1] is showing a value correctly corresponding to the Call Spreadsheet file (i.e., WSS STDID).

I'm getting mixed results and I'm not sure why....

If someone could assist, that would be awesome!

 

[Logit]

.
Question: Why is your "form" in one workbook and the data is stored in a separate workbook ? Very unorthodox.

 

[smiley]

It's the way the file system is set up. Within our SharePoint system, VBA macros can't run directly. They need to be downloaded to each person's desktop individually. However, the spreadsheet storing the data needs to be centrally located so all users can access it to store data using their desktop macro.

Convoluted, yes ... I know. But those are the constraints I'm working under. It may be unorthodox, but I'm assuming there must be some way to navigate through this.

 

[Logit]

.
I've tested the following here and it functions as desired. You will need to edit the path to meet your system requirements.

Option Explicit

Sub CommandButton1_Click()
Dim WSSID As String, dDATE As String, dTIME As String, OBCALL As String, CALLER As String, PURPOSE As String
Dim myData As Workbook
Dim RowCount As Variant
Dim Ret

On Error Resume Next
Application.ScreenUpdating = False
Worksheets("Sheet1").Select

WSSID = Range("A2")
dDATE = Range("B2")
dTIME = Range("C2")
OBCALL = Range("D2")
CALLER = Range("E2")
PURPOSE = Range("F2")

 Ret = IsWorkBookOpen("C:\Users\gagli\Desktop\Postings.xlsx")   '<-- Change path here

    If Ret = True Then
        GoTo StartDat
    Else
        Workbooks.Open ("C:\Users\gagli\Desktop\Postings.xlsx")   '<-- Change path here
    End If

StartDat:

Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("A1").Select

RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count

    With Worksheets("Sheet1").Range("A1")
        .Offset(RowCount, 0) = WSSID
        .Offset(RowCount, 1) = dDATE
        .Offset(RowCount, 2) = dTIME
        .Offset(RowCount, 3) = OBCALL
        .Offset(RowCount, 4) = CALLER
        .Offset(RowCount, 5) = PURPOSE
    End With

ActiveWorkbook.Close True
Application.ScreenUpdating = True
Range("A2:F2").Value = ""
End Sub

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

 

[smiley]

.
I've tested the following here and it functions as desired. You will need to edit the path to meet your system requirements.

Option Explicit

Sub CommandButton1_Click()
Dim WSSID As String, dDATE As String, dTIME As String, OBCALL As String, CALLER As String, PURPOSE As String
Dim myData As Workbook
Dim RowCount As Variant
Dim Ret

On Error Resume Next
Application.ScreenUpdating = False
Worksheets("Sheet1").Select

WSSID = Range("A2")
dDATE = Range("B2")
dTIME = Range("C2")
OBCALL = Range("D2")
CALLER = Range("E2")
PURPOSE = Range("F2")

Ret = IsWorkBookOpen("C:\Users\gagli\Desktop\Postings.xlsx")   '<-- Change path here

    If Ret = True Then
        GoTo StartDat
    Else
        Workbooks.Open ("C:\Users\gagli\Desktop\Postings.xlsx")   '<-- Change path here
    End If

StartDat:

Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("A1").Select

RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count

    With Worksheets("Sheet1").Range("A1")
        .Offset(RowCount, 0) = WSSID
        .Offset(RowCount, 1) = dDATE
        .Offset(RowCount, 2) = dTIME
        .Offset(RowCount, 3) = OBCALL
        .Offset(RowCount, 4) = CALLER
        .Offset(RowCount, 5) = PURPOSE
    End With

ActiveWorkbook.Close True
Application.ScreenUpdating = True
Range("A2:F2").Value = ""
End Sub

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

Thank you sooooooo much, Logit! I've got it working now, thanks to you!

 

[Logit]

.
You are welcome.