テキストボックスのマクロボタン その文字列を数値に変換しセルに入力できませんか？

BVA自己流素人です。
エクセルブック(A)のsheet1に4桁の数値の書かれたテキストボックスが多数貼り付けられています。
それぞれがマクロボタンになっていて、どれかを選択するとテキストの4桁を取得して別のエクセルブック(B)を開きシート名(数値4桁）と一致するシートを検索して表示するVBAがすでに組んであります。

Sub test1()
Dim i As String
Dim botan
bangou = Application.Caller
i = ActiveSheet.Shapes(bangou).TextFrame.Characters.Text

Workbooks.Open Filename:="c:\…B.xls"
Sheets(i).Select
End Sub

助けていただきたいのはこの後にエクセルAのsheet2　A1セルにその4桁の数値を入力したいのですがどうしてもうまくいきません。基礎知識がないため苦しんでいます。どなたか助けてください。

No.4 ベストアンサー 回答者： WindFaller 回答日時： 2017/09/23 22:53

割り込みさせていただきます。

これは、思った以上に難易度の高いマクロです。不可抗力のミスが入り込むからです。

それと、余計なお世話になってしまうかもしれませんが。質問のようなコードは、かなり古いタイプのもので、Ver.5 のテクニックで、コントロール配列を使った技術です。誰からちゃんと教わるか、その説明が書かれたテキストを手に入れない限りは、あまり、そのようなコードは求めないほうがよいと思います。せめて、フォームボタンにしたほうが分かりやすかったはずです。フォーム全体の使い方は、今は、もうMicrosoft 側にはありません。

さて、変数の名称は変更させていただきました。

Sub Test1()
Dim objName As String
Dim ShName As String
Dim wb As Workbook
objName = Application.Caller
ShName = ActiveSheet.Shapes(objName).TextFrame.Characters.Text
ShName = Application.Clean(ShName) '不要なゴミが入り込む余地をなくす
Set wb = Workbooks.Open(Filename:="c:\…B.xls")
With wb.Worksheets(ShName)
　.Select
　.Range("A1").Value = ShName
End With
With WorkBooks("A.xls") 'すでに開いていると見做す。
　.Worksheets("Sheet2").Range("A1").Value =ShName
End With
End Sub

'//
なお、微妙なところでうまくいかない可能性もあります。
シート名は、おそらく日付型だとは思いますが、私の経験では、シート名を手動で入力されたものは、一旦、マクロで、シート名を整理しなおさないと、うまくヒットしないこともあります。

Sub SheetRenaming()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
　buf = Trim(Application.Clean(sh.Name))
　sh.Name = StrConv(buf, vbNarrow)
　Debug.Print sh.Name '取得した名称は、再利用する
Next
End Sub

これですと、カタカナのシート名は半角になってしまいますから、もしその場合は、正規表現置換の方がよいでしょう。

この回答へのお礼

ありがとうございました。
非常にうまくいきました。
不安定なコードを書き直してくださった理由でベストアンサーとさせていただきます。

Goouserラック様もありがとうございました。
こちらもうまく作動いたしました。

お礼日時：2017/09/25 09:57

No.3 回答者： GooUserラック 回答日時： 2017/09/23 17:46

No.1 さらに追補

どちらの変数に代入されているかもわからなければ、「i = ActiveSheet.…」と「Workbooks.…」の間に「MsgBox ("bangou = " & bangou & " 、i = " & i)」を入れて実行してみてください。

No.2 回答者： GooUserラック 回答日時： 2017/09/23 17:37

No.1 の追補

もしどちらかの変数ならば、「i = ActiveSheet.…」と「Workbooks.…」の間に
「Sheets("sheet2").Range("A1").Value = bangou」か「Sheets("sheet2").Range("A1").Value = i」を入れればよいのでは？

No.1 回答者： GooUserラック 回答日時： 2017/09/23 17:12

「その4桁の数値」とは「bangou」という変数に代入されたものですか？それとも「i」という変数に代入されたものですか？