Q:

Getting recordset value without copying to sheet (Excel VBA)

I am putting together a userform, where a user would be able to get real-time count of resolved queries from SQL Server.
Currently, I use ADODB connection to connect to SQL table and execute a Count command. I then take the value and copy it into an Admin sheet, where it is then presented in a userform. 
I wonder if there is a way to present the Count command result without copying to an Admin sheet then to the userform?
My current code
Private Sub UserForm_Initialize()
    Set objmyconn = New ADODB.Connection
    Set objmyrecordset = New ADODB.Recordset
    Dim strSQL As String
        objmyrecordset.CursorLocation = adUseClient
        objmyconn.ConnectionString = "Provider=SQLOLEDB; Data Source=Server;Initial Catalog=DB;User ID=User;Password=Pass; Trusted_Connection=no"
        objmyconn.Open
        strSQL = "SELECT COUNT(TempStatus) FROM [DB] WHERE [TempStatus] = 'pinged'"
        Set objmyrecordset.ActiveConnection = objmyconn
        objmyrecordset.Open strSQL
        Sheets("Admin").range("A1").CopyFromRecordset (objmyrecordset)
        Me.Label1 = Sheets("Admin").range("A1").Value
        objmyconn.Close
        Set objmyconn = Nothing
        Set objmyrecordset = Nothing
End Sub

Any advise would be appreciated. Thanks
***EDIT****
I have amended code to copy val into a variable (test):
    Private Sub UserForm_Initialize()
        Set objmyconn = New ADODB.Connection
        Set objmyrecordset = New ADODB.Recordset
        Dim strSQL As String
        Dim test as variant
            objmyrecordset.CursorLocation = adUseClient
            objmyconn.ConnectionString = "Provider=SQLOLEDB; Data Source=Server;Initial Catalog=DB;User ID=User;Password=Pass; Trusted_Connection=no"
            objmyconn.Open
            strSQL = "SELECT COUNT(TempStatus) FROM [DB] WHERE [TempStatus] = 'pinged'"
            Set objmyrecordset.ActiveConnection = objmyconn
            objmyrecordset.Open strSQL
            test.CopyFromRecordset (objmyrecordset)
            Me.Label1 = test.Value
            objmyconn.Close
            Set objmyconn = Nothing
            Set objmyrecordset = Nothing
    End Sub

A:

The result of "SELECT COUNT(TempStatus) FROM [DB] WHERE [TempStatus] = 'pinged'" is still a recordset. So you have to get the value of the field of the recordset like this objmyrecordset.fields(0).value. This should give you the result of COUNT.

