VBA UserForm.Lable.Caption VALUE FOR INSERT CELL

[kar2rost]

Hello my friends
I have a form that contains these buttons
I want the form to open and to click on each of the buttons, put its caption in the cell
I tested the following code

' Function to edit the value in column B for a given cell.
Sub EditValue(cell)
    ' Initialize the UserForm1 object
    Dim UserForm1 As UserForm1
    Set UserForm1 = New UserForm1
   
    UserForm1.Show
    Set UserForm1 = Nothing
  
        cell.Offset(0, 1).Value = UserForm1.ActiveControl.Caption
 
End Sub

And I get the following error,
"object variable or with block variable not set"

code of button

Private Sub CommandButton1_Click()
Unload Me
End Sub

what is the reason?

 

[Micron]

Is "cell" a variable that you don't show as being declared in your code? It's not a valid reference to any cell if that's what you're trying to do. There is "cells" and variations of the word cell (such as ActiveCell) but there is no "cell". Also, AFAIK, Offset is a property of a range object, not a cell.

 

[kar2rost]

Is "cell" a variable that you don't show as being declared in your code? It's not a valid reference to any cell if that's what you're trying to do. There is "cells" and variations of the word cell (such as ActiveCell) but there is no "cell". Also, AFAIK, Offset is a property of a range object, not a cell.

how to fix this .can you help me by code? I want when click on each of button in form caption of button or value that ref to button insert in the cells

 

[bebo021999]

try to replace

Dim UserForm1 As UserForm1

with

Dim UserForm1 As UserForm

 

[dmt32]

I want the form to open and to click on each of the buttons, put its caption in the cell

Not fully sure understood your requirement but see if this update to your code does what you want

Place all code in your userforms code page

Private Sub CommandButton1_Click()
    EditValue Range("A2")
End Sub

Private Sub CommandButton2_Click()
    EditValue Range("A10")
End Sub

Private Sub CommandButton3_Click()
    EditValue Range("A20")
End Sub

Private Sub CommandButton4_Click()
    EditValue Range("A30")
End Sub

Private Sub CommandButton5_Click()
    EditValue Range("A40")
End Sub


' Function to edit the value in column B for a given cell.
Sub EditValue(ByVal cell As Range)
        cell.Offset(0, 1).Value = ActiveControl.Caption
End Sub

You will need to change the specified ranges in each of the Click events as required

Dave