Command Button Code Stream lining
 

Ok, I have this barbaric code for a set of 34 separate command buttons
that I am using to archive a set of data here's the code (for 1 of the
34 they all are the same code):

Private Sub CommandButton1_Click()
blah = 3
Bla = "3"
newrow
Set SourceRange = Sheets("Current Patients").Range(Cells(blah, 1),
Cells(blah, 14))
Set destrange = Sheets("Archive").Range("A3")
SourceRange.Copy destrange
Set fixrow = Sheets("Archive").Rows("3:3")
fixrow.RowHeight = 12.75
Set SourceRange = Sheets("Current Patients").Range(Cells(blah + 1,
1), Cells(34, 14))
Set destrange = Sheets("Current Patients").Range("A" + Bla)
SourceRange.Copy destrange
Cells(blah, 1).Select
End Sub

Sub newrow()

Application.CutCopyMode = False
Sheets("Archive").Rows("3:3").Insert Shift:=xlDown
Sheets("Archive").Cells(3, 15).Value = Now

End Sub

Is there a way where I can just dig up the the name so that I can use
the # in the name e.g. It is # of command button +2 I want to use.
There has to be some easy way to do this without the 34 separate
reiterations of code. Another minor question is if I can some how auto
fit the command button sizes to the size of a cell w/o manually doing
it.

Thank you,
Jeremy Donaghue


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Command Button Code Stream lining
 

for each obj in activesheet.OleObjects
if typeof Obj.Object is MSForms.commandbutton then
set rng = Obj.TopLeftCell
obj.Top = rng.top
obj.Left = rng.Left
obj.Width = rng.Width
obj.Height = rng.Height
end if
Next

Do you have to copy the rows one row at a time.

Can't you just do

Sub ArchiveData()
Dim rng As Range
Set rng = Worksheets("Current Patients") _
.Rows("3:34")
rng.Cut
Worksheets("Archive").Rows("3:3") _
.Insert Shift:=xlDown
Worksheets("Archive").Cells(3, 15) _
.Resize(rng.Rows.Count, 1).Value = Now

End Sub

If not, explain what you actually need to do. Yes you can build a generic
routine and pass a row number to it.

Regards,
Tom Ogilvy



JDonaghue wrote in message
...

Ok, I have this barbaric code for a set of 34 separate command buttons
that I am using to archive a set of data here's the code (for 1 of the
34 they all are the same code):

Private Sub CommandButton1_Click()
blah = 3
Bla = "3"
newrow
Set SourceRange = Sheets("Current Patients").Range(Cells(blah, 1),
Cells(blah, 14))
Set destrange = Sheets("Archive").Range("A3")
SourceRange.Copy destrange
Set fixrow = Sheets("Archive").Rows("3:3")
fixrow.RowHeight = 12.75
Set SourceRange = Sheets("Current Patients").Range(Cells(blah + 1,
1), Cells(34, 14))
Set destrange = Sheets("Current Patients").Range("A" + Bla)
SourceRange.Copy destrange
Cells(blah, 1).Select
End Sub

Sub newrow()

Application.CutCopyMode = False
Sheets("Archive").Rows("3:3").Insert Shift:=xlDown
Sheets("Archive").Cells(3, 15).Value = Now

End Sub

Is there a way where I can just dig up the the name so that I can use
the # in the name e.g. It is # of command button +2 I want to use.
There has to be some easy way to do this without the 34 separate
reiterations of code. Another minor question is if I can some how auto
fit the command button sizes to the size of a cell w/o manually doing
it.

Thank you,
Jeremy Donaghue


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/