Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
command button code | Excel Discussion (Misc queries) | |||
VBA code behind command button | Excel Worksheet Functions | |||
Command Button VBA code | Excel Discussion (Misc queries) | |||
generating sheet with command button & code | Excel Programming | |||
Create Command Button from Code | Excel Programming |