VBA/Macro code to get the value of textbox

Question

Sub CopyRandomRows()

 Windows("sample rnd.xlsm").Activate
    Rows("1:1").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Windows("rnd sample draft.xlsm").Activate
    Sheets("Random Sample").Select
    Rows("1:1").Select
    ActiveSheet.Paste


  Dim source As Range, target As Range, randCount&, data(), value, r&, rr&, c&


  Set source = Workbooks("sample rnd.xlsm").Worksheets("Sheet1").Range("A2:L5215")

  Set target = Workbooks("rnd sample draft.xlsm").Worksheets("Random Sample").Range("A2")

  randCount = 5

  data = source.value


  For r = 1 To randCount
    rr = 1 + Math.Round(VBA.rnd * (UBound(data) - 1))
    For c = 1 To UBound(data, 2)
      value = data(r, c)
      data(r, c) = data(rr, c)
      data(rr, c) = value
    Next
  Next


  target.Resize(randCount, UBound(data, 2)) = data

End Sub

This is my code. My problem is that I can only change the number of data i want if i change the code randCount = 5. I want to be able to use my TextBox and use it for defining how many data to get. I tried randCound = TextBox1.value and randCount = TextBox1.Text but does not seem to work. What am i missing? How do i get it work. thanks in advance

Where is the TextBox1 located? Is it on a sheet? Which sheet? Is it in a form (name of the form)? Are you using a form control or an ActiveX control? — Ralph
– Ralph, Commented Mar 18, 2016 at 1:19

ThunderFrame · Accepted Answer · 2016-03-18 02:03:03Z

15

If TextBox1 is on a sheet named Main, then you can use this:

Worksheets("Main").TextBox1.Value

Better yet, you can give the sheet a CodeName of something like shtMain and then use

shtMain.TextBox1.Value

And finally, you can also get to the textbox through the shapes collection (but the methods above are preferable)...

Worksheets("Main").Shapes("TextBox1").OLEFormat.Object.Object.Value

answered Mar 18, 2016 at 2:03

ThunderFrame

9,5162 gold badges31 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ThunderFrame Over a year ago

hmm, maybe add it as a new question, and provide more details about what you're trying to do

markerbean Over a year ago

oh. didn't know that will work. I have tried this before and figured out that the problem was that I wasn't able to open the file first where i should get the data. thank you! simplest answer but does the thing. I already posted another question for my other problem. i hope you could help me out again

Nayan Over a year ago

It should be -> Worksheets("Main").Shapes("TextBox1").OLEFormat.Object.Value

TunaLobster · Accepted Answer · 2016-03-18 01:20:14Z

2

You should be able to use foo = InputBox("bar").

If however you are getting it from a UserForm then it would be foo = UserForm1.TextBox1.Value

answered Mar 18, 2016 at 1:20

TunaLobster

613 bronze badges

Collectives™ on Stack Overflow

VBA/Macro code to get the value of textbox

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related