Storing Cell Value into a Variable

[rcb007]

I hope this is not to hard.

I am trying to take an active cell value and place it in a memory as "USERS1" Variable.

Then, once its stored I am passing it to another program like autocad, so it would place that value in the command line.

Public Sub PasteCurrentCell()

    Dim sh As Excel.Worksheet
    Dim rng As Excel.Range
    
    Set sh = GetExcelSheet()
    If sh Is Nothing Then
        MsgBox "Excel is not running, or" & vbCrLf & _
        "opened Excel file does not have ""SHEET1""."
        Exit Sub
    End If
    
    Set rng = sh.Range("A2") '<<<<<---- How can I make this select the current active cell?? it could be in any column or row.
    rng.Copy
    
    InsertCurrentCellValue

End Sub

Private Function GetExcelSheet() As Excel.Worksheet

    Dim theSheet As Excel.Worksheet
    Dim sh As Excel.Worksheet
    Dim xls As Excel.Application
    
    On Error Resume Next
    
    Set xls = GetObject(, "Excel.Application")
    If Not xls Is Nothing Then
    
        For Each sh In xls.ActiveWorkbook.Worksheets
            If UCase(sh.Name) = "SHEET1" Then
                Set xls.ActiveSheet = sh
                Set theSheet = sh
                Exit For
            End If
        Next
        
    End If
    
    Set GetExcelSheet = theSheet
    
End Function

Private Sub InsertCurrentCellValue()
 On Error Resume Next
 Set AcadApp = GetObject(, "AutoCAD.Application")
 If Err Then
 Err.Clear
 Set AcadApp = CreateObject("AutoCAD.Application")
 End If
 AppActivate AcadApp.Caption
 AcadApp.Visible = True
 AcadApp.Application.WindowState = acNorm
 AcadApp.ActiveSpace = acModelSpace
 If AcadApp.Documents.Count = 0 Then
 AcadApp.Documents.Add
 End If
    ThisDrawing.SetVariable "USERS1", strcname '<<<<<------ Can I get the Current Active Cell to be stored in a "USERS1" Varible?
    AcadApp.ActiveDocument.SendCommand "zm2st" & vbCr
    
End Sub

 

[Joe4]

If you simply want to make "rng" the current active cell, just change this line:

Set rng = sh.Range("A2")

to this:

Set rng = ActiveCell

If you want to pass values from one procedure to the next, you will either need to set-up input arguments into the procedures you want to receive the passed value, or use Global/Public variables.

 

[rcb007]

If you simply want to make "rng" the current active cell, just change this line:

Set rng = sh.Range("A2")

to this:

Set rng = ActiveCell

If you want to pass values from one procedure to the next, you will either need to set-up input arguments into the procedures you want to receive the passed value, or use Global/Public variables.

Thank you for that piece! Hit a wall trying to figure it out.