マクロ ボタンに名前をつける

質問

☆シート１にボタンが29個あり、
ボタンを押すとシート２やシート３のページをアクティブにして表示するマクロを作っています。
その過程で、シート１にボタンを29個作成するところまでは終わりました。
そして、次にボタン１つ１つに名前を付けたいと思うのですが、
シート２、３、４...のI2のテキストを引用してボタンの名前を付けたいです。
（各シートのタイトルはI2に記入しているため。）

シート２のI2のテキストを引用してボタンに名前を付けるマクロを作ってみましたが、
”広告事業収入”がシート２だとは限らないので、”広告事業収入”を引用せず、
”シート２のI2”を引用してボタンの名前にするマクロにしていただきたいです。

・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・

Sub シート2のボタン作成()
'
Sheets(2).Select
Range("I2").Select
ActiveCell.FormulaR1C1 = "広告事業収入"
Sheets(1).Select
ActiveSheet.Shapes.Range(Array("Button 1")).Select
Selection.Characters.Text = "広告事業収入"
With Selection.Characters(Start:=1, Length:=9).Font
.Name = "ＭＳ Ｐゴシック"
.FontStyle = "標準"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

End Sub

・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・

いらない部分も削除していただけたら幸いです。

回答No.4

mt2008 · ベストアンサー

Worksheets.Countでそのブックのワークシート数が取得できます。
それを利用して以下のようにするとワークシート枚数に応じて横に8個づつ並べて表示する事が出来ます。

Sub ボタン設置3()
　　With Worksheets(1)
　　For i = 1 To (Worksheets.Count - 1)
　　　　nX = 145 * (1 + ((i - 1) Mod 8))
　　　　nY = 90 * (1 + Int(i / 9))
　　　　.Buttons.Add(nX, nY, 140, 20).Text = Worksheets(i + 1).Range("I2").Value
　　Next i
　　End With
End Sub

お礼

ご提案とご回答本当にありがとうございました！
回答者様のやり方で、一気に作業ができ、感謝しております。
やってみたところ、順番が少しいれ変わるボタンがあったのですが、
自分で考えてみます！
また質問することがあると思いますので、その際には
助言いただけますと幸いです。
本当にありがとうございました！

回答No.3

keithin

ん？　あぁ、シート２じゃなくて２枚目のシートですね。前のご相談で教わっていた事ですよ。

sub ２枚目のシートのボタン作成()
　dim s as string
　s = worksheets(2).range("I2").value

　with activesheet.shapes(application.caller).textframe.characters
　　.text = s
　　.font.name = "ＭＳ Ｐゴシック"
　　.font.size = 11
　end with
　worksheets(2).name = s
end sub

お礼

ご回答ありがとうございました。

回答No.2

mt2008

これ↓で作ったボタンに名前を付けるという事でしょうか？
http://okwave.jp/qa/q8983229.html

でしたら、作った後に名前を付けるのではなく、作るときに名前を付けた方が良いでしょう。
先のサンプルでは1から連番の名前を付けています。

Sub Sample()
　　With Worksheets(1)
　　　　For i = 1 To 29
　　　　　　.Buttons.Add(50 * i, 10, 30, 20).Text = Worksheets(i + 1).Range("I2").Value
　　　　Next i
　　End With
End Sub

補足

回答者様のやり方をすることにしました！ご提案ありがとうございます！

そこで、質問なのですが、
「ボタンを横一列に並べるのではなく、８個ずつ並べて３段にする」ように変更しました。
目次を除けたシートは、２３枚なので、１段目、２段目は８ずつ並べることができたのですが、
３段目は７個であるため、エラーになりました。
３段目のボタンを付けるマクロの時に、
For i = 1 To 8　を For i = 1 To 7　に変えれば大丈夫だとは思うのですが、
今後もシートの枚数が１、２枚変更する予定があるので、
最後のボタンを作成するマクロに
「シートが終わるまでボタンをつける」や「シートが終わればボタンはそれ以上作らない」
といったような指示もしたいと考えています。

よろしければ、下記のマクロに付け加えていただければ嬉しいです。

・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・

Sub ボタン設置3()
With Worksheets(1)
For i = 1 To 8
.Buttons.Add(145 * i, 90, 140, 20).Text = Worksheets(i + 17).Range("I2").Value
Next i
End With
End Sub

回答No.1

keithin

sub シート２のボタン作成()
　dim s as string
　s = worksheets("Sheet2").range("I2").value

　with activesheet.shapes(application.caller).textframe.characters
　　.text = s
　　.font.name = "ＭＳ Ｐゴシック"
　　.font.size = 11
　end with
　worksheets("Sheet2").name = s
end sub

エクセルには「フォーム」と「アクティブＸコントロール」の２種類のコマンドボタンがあります。それぞれ使い方も書くべき具体的なマクロも違ってくるので，あなたが今どちらを使っているのか，また寄せられた各回答がどちらを前提にしているのか，間違えないようによく気をつけてください。

お礼

ご回答ありがとうございます。
こちらの質問が稚拙で分かりにくく失礼いたしました。

補足

実行してみましたが、エラーになってしまいました。
デバッグ？では
s = worksheets("Sheet2").range("I2").value
がエラー対象のようです。

こちらが最初に提示したマクロが間違っていたのだと思いますが、
どのようにしたら良いのかご教授願います。