CODEWITHSUNDEEP
vbaexcel
Tim Wilkinson
Tim Wilkinson

Reputation: 3791

VBA Text Box not returning entered value

I have the following code that copies a number of lines to a different point in the sheet. At present the number of lines is defined by the user by way of a cell value, however I am trying to change to using a text box to enter the value, not a cell.

I added a message box to check the value of the text box, but regardless what number I enter it always returns as 0.

Dim NumofRows As Integer

NumofRows = CInt(TextBox1)

MsgBox (NumofRows)

If NumofRows = 0 Then
    NumofRows = 1
End If

Range("A500").End(xlUp).Offset(1).Select
ActiveCell.Rows("1:" & NumofRows).EntireRow.Copy
Range("A2").Select
Selection.End(xlDown).Offset(1).EntireRow.Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

The textbox is called TextBox1. Can anyone suggest how to correct this? Many thanks.

Upvotes: 0

Views: 681

Answers (1)

Paidjo
Paidjo

Reputation: 111

I guess you have a text box on a sheet. Please try this : example you have "Sheet1"

NumofRows = CInt(Sheets("Sheet1").TextBox1.Value)

Upvotes: 2

Related Questions