我该如何做?[求助]

chill

在excel中我要COPY ”命令按钮” , 并且还要COPY原 “命令按钮”中执行的内容,怎么办? 如下: 只能COPY 命令按钮,形成一个CommandButton2,但CommandButton1中的click事件不可以Copy. 我该如何做呢? THANKS! Sheet1.CommandButton1.copy an = 13 + 23 * i bn = 14 + 23 * i range4 = "B" + Format(an) + ":" + "C" + Format(bn) Range(range4).Select ActiveSheet.Paste [em15]

rowen

'下列过程可在模块1 中插入三行代码,供借鉴.如果你有打开多个代码窗口,则可能在其中一个窗口中,不一定是模块1 Private Sub CommandButton1_Click() Dim MyCodeLine(3) As String MyCodeLine(1) = "Private Sub Sample()" MyCodeLine(2) = " an = 13 + 23 * i" MyCodeLine(3) = "End Sub" For i = 1 To 3 '如果在当前窗口插入代码,可用 .SelectedVBComponent 代替 .CodePanes(1) Application.VBE.CodePanes(1).CodeModule.InsertLines i, MyCodeLine(i) Next End Sub

chill

Mr. 对于你提供的代码我不是很熟悉.你可不可以检查一下我的代码.帮我修正一下好吗?Thanks! Sub copyclear(i As Integer) Dim an, bn As Integer Dim range1, range2, range3, range4 As String Sheet1.CommandButton1.copy an = 13 + 23 * i bn = 14 + 23 * i range4 = "B" + Format(an) + ":" + "C" + Format(bn) Range(range4).Select ActiveSheet.Paste ‘ 以上为COPY命令按钮.每copy一次,命令按钮自动递增,如 CommandButton1,CommandButton2,CommandButton3…… Dim MyCodeLine(3) As String MyCodeLine(1) = "Private Sub CommandButton(i as integer)" MyCodeLine(2) = " an = 13 + 23 * i" ‘ 这一行我不理解 MyCodeLine(3) = "End Sub" For i = 1 To 3 Application.VBE.CodePanes(1).CodeModule.InsertLines i, MyCodeLine(i) Next End Sub ‘我已经写了一个模块,为CommandButton(I as interger),其中i为参数.每copy一次按钮,其中也会copy这个模块. 谢谢你啦!

rowen

MyCodeLine(2)只是用了你现成的一句,实际可插入任意需要的代码. 可以的话,把你的代码帖出来看看,一起学习学习. 还有个问题:既然每个CommandButton执行一样的代码,为什么不能使用同一个CommandButton?

chill

对不起, 每个命令按钮的代码不是完全相同.只是有一定的规律.与参数i有关.我的代码如下: Sub copyclear(i As Integer) Dim an, bn As Integer Dim range1, range2, range3 As String Range("A3:AZ25").Select Selection.copy an = 3 + 23 * i bn = 25 + 23 * i range1 = "A" + Format(an) + ":" + "AZ" + Format(bn) Range(range1).Select ActiveSheet.Paste an = 5 + 23 * i bn = 12 + 23 * i range2 = "A" + Format(an) + ":" + "AZ" + Format(bn) Range(range2).Select Selection.ClearContents Application.CutCopyMode = False an = 15 + 23 * i bn = 24 + 23 * i range3 = "D" + Format(an) + ":" + "AZ" + Format(bn) Range(range3).Select Application.CutCopyMode = False Selection.ClearContents Sheet1.CommandButton1.copy an = 13 + 23 * i bn = 14 + 23 * i range4 = "B" + Format(an) + ":" + "C" + Format(bn) Range(range4).Select ActiveSheet.Paste Dim MyCodeLine(3) As String MyCodeLine(1) = "Private Sub CommandButton(i as integer)" MyCodeLine(2) = " an = 13 + 23 * i" ‘ 这一行我不理解 MyCodeLine(3) = "End Sub" For i = 1 To 3 Application.VBE.CodePanes(1).CodeModule.InsertLines i, MyCodeLine(i) Next End Sub Sub CommandButton(i As Integer) Dim begin_date As Date Dim myrange, range1, range2, range3 As Range, myfind As Range Dim aa, bb, an, bn, cn, dn, en, fn As Integer If CommandButton1.Locked = False Then begin_date = Date an = 13 + 23 * i Cells(an, 4) = begin_date Set myrange = ActiveSheet.Rows(an) bn = 3 + 23 * i range1 = "F" + Format(bn) Set myfind = myrange.Find(what:=Range(range1).Value, LookIn:=xlValues, MatchCase:=False) If Not myfind Is Nothing Then mycolumn = myfind.Column aa = mycolumn '预交日所在栏 End If '------装配排程 bb = Int(Cells(bn, 10)) '预计工作日取整 cc = Cells(bn, 13) - 1 '与预交日相隔的天数 dd = Cells(bn, 11) '预计工作日 cn = 23 + 23 * i If cc >= dd Then Do Until bb = 0 Cells(cn, aa - dd) = Cells(bn, 8) bb = bb - 1 dd = dd - 1 Loop Cells(cn, aa - dd) = Cells(bn, 4) - Cells(bn, 8) * Int(Cells(bn, 10)) Else bb = Int(Cells(bn, 10)) dd = 5 Do Until bb = 0 Cells(cn, dd) = Cells(bn, 8) bb = bb - 1 dd = dd + 1 Loop MsgBox "注意:你必须要按排加班时间!" Cells(an, mycolumn - 1).Interior.ColorIndex = 3 Cells(cn, mycolumn - 1) = Cells(cn, mycolumn - 1) + (Cells(bn, 4) - Cells(bn, 8) * Int(Cells(bn, 10))) End If '------黑身排程 dn = 21 + 23 * i If Cells(dn, 2) <> 0 Then '若有托外情况 For i = 1 To 4 bb = Int(Cells(bn, 10)) '预计工作日取整 cc = Cells(bn, 13) - 1 '与预交日相隔的天数 dd = Cells(bn, 11) '预计工作日 If cc - i >= dd Then Do Until bb = 0 Cells(cn - 2 * i, aa - dd - i) = Cells(bn, 8) bb = bb - 1 dd = dd - 1 Loop Cells(cn - 2 * i, aa - dd - i) = Cells(bn, 4) - Cells(bn, 8) * Int(Cells(bn, 10)) Else bb = Int(Cells(bn, 10)) dd = 5 Do Until bb = 0 Cells(cn - 2 * i, dd) = Cells(bn, 8) bb = bb - 1 dd = dd + 1 Loop MsgBox "注意:你必须要按排加班时间!" Cells(an, mycolumn - 1 - i).Interior.ColorIndex = 3 Cells(cn - 2 * i, mycolumn - 1 - i) = Cells(cn - 2 * i, mycolumn - 1 - i) + (Cells(bn, 4) - Cells(bn, 8) * Int(Cells(bn, 10))) End If Next i Else '若没有托外 For i = 1 To 3 bb = Int(Cells(bn, 10)) '预计工作日取整 cc = Cells(bn, 13) - 1 '与预交日相隔的天数 dd = Cells(bn, 11) '预计工作日 If cc - i >= dd Then Do Until bb = 0 Cells(dn - 2 * i, aa - dd - i) = Cells(bn, 8) bb = bb - 1 dd = dd - 1 Loop Cells(dn - 2 * i, aa - dd - i) = Cells(bn, 4) - Cells(bn, 8) * Int(Cells(bn, 10)) Else bb = Int(Cells(bn, 10)) dd = 5 Do Until bb = 0 Cells(dn - 2 * i, dd) = Cells(bn, 8) bb = bb - 1 dd = dd + 1 Loop MsgBox "注意:你必须要按排加班时间!" Cells(an, mycolumn - 1 - i).Interior.ColorIndex = 3 Cells(dn - 2 * i, mycolumn - 1 - i) = Cells(dn - 2 * i, mycolumn - 1 - i) + (Cells(bn, 4) - Cells(bn, 8) * Int(Cells(bn, 10))) End If Next i End If CommandButton1.Locked = True Else Dim response response = MsgBox("已经排程!需重新排程吗?", vbYesNo) If response = vbYes Then CommandButton1.Locked = False en = 15 + 23 * i fn = 24 + 23 * i range2 = "D" + Format(en) + ":" + "AZ" + Format(fn) range3 = "D" + Format(an) + ":" + "AZ" + Format(an) Range(range2).ClearContents Range(range3).Interior.ColorIndex = 2 MsgBox "请再按一次!" End If End If End Sub

chill

我里面还包括ACCESS编程.是一个大的数据库,只是在调用excel做其中的一小部分,就如我上面粘的数据.并且还没完成.给你有用吗?我已傳給你啦!

[此贴子已经被作者于2002-1-31 14:04:28编辑过]

dick

將放在模組中,在用call 方式取用 例 Sub copy1() an = 13 + 23 * i bn = 14 + 23 * i range4 = "B" + Format(an) + ":" + "C" + Format(bn) Range(range4).Select ActiveSheet.Paste End Sub ******************************* Private Sub CommandButton1_Click() Call copy1 End Sub ********************************* Private Sub CommandButton2_Click() Call copy1 End Sub

chill

但是,我的commandbutton2根本还没产生,程序是与其同时产生的,其实我所调用的模块中的参数是与commsndbutton的个数有关的.

guan_haibo

想必4年的时间chill兄弟已经成长为高手啦  你问上面问题的时候我还在大学里面茫然呢 

[em02]

feihong

可以把你的文件整个发给我吗？