CODEWITHSUNDEEP
excelvba
10101
10101

Reputation: 2402

Paste text from UserForm textbox to corresponding cell to ActiveCell on another Worksheet

I want to get activecell address and then use it to input text from userform textbox on another Sheet in the same Workbook. I have tried to get it working with code below but it is not working.

So I have tried to Set acad = ActiveCell.Address and use it in ThisWorkbook.Worksheets("Data").Range(acad).Value = "hello"

Here is my solution:

Private Sub CommandButton25_Click()

Dim r As Range
Dim acad As Range

On Error GoTo noval

Set r = Cells.SpecialCells(xlCellTypeAllValidation)

Set acad = ActiveCell.Address

If Intersect(r, ActiveCell) Is Nothing Then GoTo noval

ActiveCell.Validation.Delete

With ActiveCell.Validation
 .Add Type:=xlValidateInputOnly
 .InputTitle = Me.TextBox1.Value
 .InputMessage = Me.TextBox2.Value
 End With

 ThisWorkbook.Worksheets("Data").Range(acad).Value = "hello"

 ActiveCell.Offset(0, 1).Interior.ColorIndex = 4

Exit Sub

noval:

With ActiveCell.Validation
 .Add Type:=xlValidateInputOnly
 .InputTitle = Me.TextBox1.Value
 .InputMessage = Me.TextBox2.Value
 End With

 ActiveCell.Offset(0, 1).Interior.ColorIndex = 4

On Error GoTo 0

'ActiveCell.Value = ActiveCell.Value & "   " & ""

End Sub

Upvotes: 0

Views: 229

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Your assignment (Set acad = ...) is from a string to an object, which probably gives you a 424 error Object Required, because Address returns a string, not a Range object.

You'd probably then need to do: ThisWorkbook.Worksheets("Data").Range(acad.Address).Value = "hello"

Private Sub CommandButton25_Click()
Dim r As Range
Dim acad As Range

Set r = Cells.SpecialCells(xlCellTypeAllValidation)
Set acad = ActiveCell

With acad.Validation
    .Delete
    .Add Type:=xlValidateInputOnly
    .InputTitle = Me.TextBox1.Value
    .InputMessage = Me.TextBox2.Value
End With
acad.Offset(0, 1).Interior.ColorIndex = 4

ThisWorkbook.Worksheets("Data").Range(acad.Address).Value = "hello"

End Sub

Upvotes: 1

Related Questions