VBA code to generate VBA code

[Elemental]

i was thinking of making a VBA button that selects data on a sheet and copies just the values to another sheet( range from A1 to E100 ), but on the newly created sheet, i wanted to move the sheet to the end of the list, generate a new button on that sheet, and setup the code/properties for it to clear just the data on that temporary sheet

of course i could have a print button for that sheet too, but that's not the hard part



/////////////////////////////////
Private Sub MakeCopy_Click()

' Mysterious Code

End Sub
/////////////////////////////////

thanks ppl

 

[Elemental]

ummm...

hehe

it seems i forgot to ask how to do any of the above in my question



i dunno where to start, can anybody help with coding to do this ?

 

[Ivan F Moala]

I guess the easiest way to do this without
creating code to create code (Which can be done) is;

1) Create a template of the sheet you want together with the commandbutton and underlying sheet code eg

<pre/>
Private Sub CommandButton1_Click()
[A1:E100].Clear
End Sub
</pre>

2) Hide this sheet Xlsheetveryhidden
3) Now code it so that you Copy this template and place it @ the end.

eg

<pre/>
Sub CopySheet()
Dim CpyRg As Range

Set CpyRg = ActiveSheet.Range("A1:E100")
Application.ScreenUpdating = False

With Sheet1
.Visible = xlSheetVisible
.Copy After:=Sheets(Sheets.Count)
.Visible = xlSheetVeryHidden
End With

CpyRg.Copy

With ActiveSheet
.Range("A1").Select
.Paste
End With

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With

End Sub
</pre>

 

[Joe Was]

Tall order a lot of code, this is how I do it. See code notes. JSW

Sub Builder()
'Note: Only run this on a blank workbook.
'The blank workbook should have three blank sheets,
'named "Sheet1, Sheet2 and Sheet3."
'Only run "Builder" once, to create the application.

Worksheets("Sheet1").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "Utility Log"
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 22
End With
Range("A2").Select
ActiveCell.FormulaR1C1 = "To use, press the button below!"
Range("A10").Select
ActiveCell.FormulaR1C1 = "Note: Row 3 on the (Data)Sheet must have valid start values!"
Range("D1").Select

ActiveSheet.Buttons.Add(81, 60.75, 175.5, 33.75).Select
Selection.OnAction = "Sheet1.Utilities"
With Selection.Characters(Start:=1, Length:=23).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.ColorIndex = 9
End With
Selection.Characters.Text = "Record Utility Readings"
Worksheets("Sheet1").Select
Range("D1").Select

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Worksheets("Sheet3").Select
ActiveWindow.SelectedSheets.Delete
Worksheets("Sheet1").Select
Sheets("Sheet1").Name = "Menu"
Range("A1:G1").Select
Worksheets("Sheet2").Select
Sheets("Sheet2").Name = "Data"
Application.ScreenUpdating = True
Application.DisplayAlerts = True

With Worksheets("Data")
.Range("B1").Value = "1st Utility"
.Range("B2").Value = "Reading"
.Range("B1:B2").Font.Bold = True
.Range("B1:B2").HorizontalAlignment = xlCenter
.Range("B3").Value = "999999"
.Range("C1").Value = "Usage"
.Range("C2").Value = "This"
.Range("C3").Value = "Period"
.Range("C1:C3").Font.Bold = True
.Range("C1:C3").HorizontalAlignment = xlCenter
.Range("E1").Value = "2nd Utility"
.Range("E2").Value = "Reading"
.Range("E1:E2").Font.Bold = True
.Range("E1:E2").HorizontalAlignment = xlCenter
.Range("E3").Value = "999999"
.Range("F1").Value = "Usage"
.Range("F2").Value = "This"
.Range("F3").Value = "Period"
.Range("F1:F3").Font.Bold = True
.Range("F1:F3").HorizontalAlignment = xlCenter
.Range("H1").Value = "3rd Utility"
.Range("H2").Value = "Reading"
.Range("H1:H2").Font.Bold = True
.Range("H1:H2").HorizontalAlignment = xlCenter
.Range("H3").Value = "999999"
.Range("I1").Value = "Usage"
.Range("I2").Value = "This"
.Range("I3").Value = "Period"
.Range("I1:I3").Font.Bold = True
.Range("I1:I3").HorizontalAlignment = xlCenter
.Range("B3").Font.ColorIndex = 11
.Range("E3").Font.ColorIndex = 11
.Range("H3").Font.ColorIndex = 11
.Range("A1").Select
End With
Sheets("Menu").Select
Range("D1").Select
End Sub

Sub Utilities()
Dim clickTest As Variant
Dim my1st As Variant
Dim my2nd As Variant
Dim my3rd As Variant
Dim my1stD As Variant
Dim my2ndD As Variant
Dim my3rdD As Variant
Dim my1stO As Variant
Dim my2ndO As Variant
Dim my3rdO As Variant

'Open checkbox menu.
With Assistant.NewBalloon
.Heading = "Start utility reading;"
.Text = "Click the box which is your choice:"

'Build three check boxes.
For i = 1 To 3
.CheckBoxes(1).Text = ("1st Utility.")
.CheckBoxes(2).Text = ("2nd Utility.")
.CheckBoxes(3).Text = ("3rd Utility.")
Next
.Button = msoButtonSetOkCancel
.Show
clickTest = 0
On Error GoTo myEnd

'Test for which box is checked.
If .CheckBoxes(1).Checked = True Then
clickTest = clickTest + 1
Worksheets("Menu").Activate

'Get new reading.
my1st = Application.InputBox(prompt:="What is the new reading for the 1st Utility?" & Chr(13) & Chr(13) & _
"If not reporting a reading, enter: 0 or press Cancel!", Title:="Enter Utility Reading:", Default:="0", Type:=1)

'Test for errors.
If my1st = 0 Then GoTo myStop
If Cancel = True Then GoTo myStop

'Do data updating and math.
Application.ScreenUpdating = False
Worksheets("Menu").Select
Worksheets("Data").Range("B65536").End(xlUp).Offset(1, 0).Value = my1st
my1stO = Worksheets("Data").Range("B65536").End(xlUp).Offset(-1, 0).Value
my1stD = my1st - my1stO
Worksheets("Data").Range("C65536").End(xlUp).Offset(1, 0).Value = my1stD
Application.ScreenUpdating = True
End If

'Display results.
If my1st <> 0 And my1stO <> 0 Then
MsgBox prompt:="The current reading is: " & my1st & Chr(13) & _
"The last reading was: " & my1stO & Chr(13) & Chr(13) & _
"The 1st utility usage this period is: " & my1stD, _
Title:="Current Usage!"
End If

'The next two check boxes are designed as above!
If .CheckBoxes(2).Checked = True Then
clickTest = clickTest + 1
Worksheets("Menu").Activate
my2nd = Application.InputBox(prompt:="What is the new reading for the 2nd Utility?" & Chr(13) & Chr(13) & _
"If not reporting a reading, enter: 0 or press Cancel!", Title:="Enter Utility Reading:", Default:="0", Type:=1)
If my2nd = 0 Then GoTo myStop
If Cancel = True Then GoTo myStop
Application.ScreenUpdating = False
Worksheets("Menu").Select
Worksheets("Data").Range("E65536").End(xlUp).Offset(1, 0).Value = my2nd
my2ndO = Worksheets("Data").Range("E65536").End(xlUp).Offset(-1, 0).Value
my2ndD = my2nd - my2ndO
Worksheets("Data").Range("F65536").End(xlUp).Offset(1, 0).Value = my2ndD
Application.ScreenUpdating = True
End If
If my2nd <> 0 And my2ndO <> 0 Then
MsgBox prompt:="The current reading is: " & my2nd & Chr(13) & _
"The last reading was: " & my2ndO & Chr(13) & Chr(13) & _
"The 1st utility usage this period is: " & my2ndD, _
Title:="Current Usage!"
End If

If .CheckBoxes(3).Checked = True Then
clickTest = clickTest + 1
Worksheets("Menu").Activate
my3rd = Application.InputBox(prompt:="What is the new reading for the 3rd Utility?" & Chr(13) & Chr(13) & _
"If not reporting a reading, enter: 0 or press Cancel!", Title:="Enter Utility Reading:", Default:="0", Type:=1)
If my3rd = 0 Then GoTo myStop
If Cancel = True Then GoTo myStop
Application.ScreenUpdating = False
Worksheets("Menu").Select
Worksheets("Data").Range("H65536").End(xlUp).Offset(1, 0).Value = my3rd
my3rdO = Worksheets("Data").Range("H65536").End(xlUp).Offset(-1, 0).Value
my3rdD = my3rd - my3rdO
Worksheets("Data").Range("I65536").End(xlUp).Offset(1, 0).Value = my3rdD
Application.ScreenUpdating = True
End If
If my3rd <> 0 And my3rdO <> 0 Then
MsgBox prompt:="The current reading is: " & my3rd & Chr(13) & _
"The last reading was: " & my3rdO & Chr(13) & Chr(13) & _
"The 1st utility usage this period is: " & my3rdD, _
Title:="Current Usage!"
End If

'Test for errors and other events.
If .CheckBoxes(i).Checked = False And clickTest <> 1 Then GoTo Emp
End With
End
myStop:
MsgBox prompt:="Operator ended update, no action taken!", Title:="UpDate Stopped!"
End
Emp:
MsgBox prompt:="You did not check an Utility box.", Title:="Input Data Error!"
End
myEnd:
End Sub

 

[Elemental]

i was just after more info on manipulating :


ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=200, Top:=200, Width:=75, Height:= _
25.5).Select

and then adding somthing that renamed the commandbutton1 to "ClearDATA", and changed the caption to "Clear Data".

i dont have any help files and Office XP assures me they're installed, so i was gonna ask sombody who mighta done it before.

BTW, Whoa that's a lot of code...

 

[Gregor]

sorry this isn't particularly related....but i was curious....do you guys have day jobs or what's the deal? I know sitting around writing code for other people doesn't sound like my idea of fun, but that's probably cause i'm no good at it

 

[Elemental]

i can understand the reasons behind hiding the sheet so others cant see it, but that kind of privacy isnt need in this workbook

i just thought i could make it a little bit more dynamic, as the command buttons to be created could be selected through case arguments then, rather than making everything set in place and just unhiding buttons.

both methods work though, i just dont know how to use the button creating method

 

[Elemental]

i'm at my day job



building excel sheets is just my way of tidying things up

theres too much paper work around here...

 

[Ivan F Moala]

On 2002-09-12 22:11, Elemental wrote:
i can understand the reasons behind hiding the sheet so others cant see it, but that kind of privacy isnt need in this workbook

i just thought i could make it a little bit more dynamic, as the command buttons to be created could be selected through case arguments then, rather than making everything set in place and just unhiding buttons.

both methods work though, i just dont know how to use the button creating method

Suggested hiding the sheet so that IF others are using it then this can't be tampered with
If its for your own use then by all means leave it visible....the choice is yours.
Whilst you can code this to dynamically create the buttons etc, it is probably a little easier to set it up as I suggested in case you need to change things around. The whole coding route can get complicated.

 

[Elemental]

yeah

coding is set in its place too, but im just getting too far ahead of what i wanna do



i can dream can't i ?

i should just leave the sheet blank with the necessary buttons and paste data into it when needed.

there's prolly no need for all the uber-code that makes everything just appear from nowhere

u guys are the best though

where would the bulk of excel code get learnt without the MVP's and the MrExcel msgboard ?

thanks for everyone's help