Change Macro Code with ActiveX Text Box

[Appleboy]

Hi there,

Is it possible to input an ActiveX control text box in my spreadsheet to change a certain name within my macro? For example,

If Range("C63").Value = "1" Then
    Windows("2.xls").Activate
    Range("B3:J3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("WEEK40'11").Activate
    Range("C4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    End If

I want to change the WEEK40'11 inside this macro to WEEK41'11 when I type in WEEK41'11 to the ActiveX control text box.

Please help, thanks.

 

[Saagar]

Yes. For example, if your activex text box is named textbox1, textbox1.value will return the value in the text box.

In this case, your code will be:
Windows(textbox1.value).Activate

Hope this helps.

 

[Appleboy]

Hey there,

Thanks for the help. Sorry but I am rather new to excel and the whole VBA. I am wondering how can I create the text box and use that code to call?

Edit: Not sure if my text box is correct. But when I included a text box and typed in the value, it gave me an error 424 which state object is required.

This Windows("WEEK40'11").Activate is to go to the file where my macro is stored which is WEEK40'11.

Please help!

 

[Appleboy]

Hi,

After much research I solved the problem myself. Using the code you provided for me, I added a Sheet1 infront of Textbox1. The code is now,

Windows(Sheet1.TextBox1.Value).Activate

Seems like you need to include where is the TextBox1 is located or else it wouldn't work.

Thanks for the help!