Use VBA to Write Code that Creates Sub(s) On The Fly

"A peg lower than Novice" -- I've setup over 200 command buttons with each lying on top of a cell in a column. The buttons are named using that column (the column being operated on) such as cmdToValues_KB. I created a small sub to:

• copy the column range
• paste result values to replace the formulas in each cell of the column range
• change the button backcolor to indicate the process was completed for that column range
• focus the cell under the button (not included in the copy/paste range)
• and activate another sheet (done in sub where date change prompting to be used or automated)

...all of which works as expected.
What I'd like to discover is how to use VBA to write the code below on the fly, but using the column designation letters in the corresponding places in the sub. For example (partial approximation)...

• Build a line concatenating:
  
  
  • Private Sub cmdToValues_
  • AC (example column letters in a variable?)
  • with _Click()

  
  

• (insert editor linefeed)
• Build several lines of code eactly as shown below which would otherwise be 100% redundant (with editor linefeeds)
• Build lines of code concatenating code fragments such as (single line example):
  
  
  • Range("
  • AC (column letters)
  • 10:
  • AC (column letters)
  • 247").Copy

  
  

• (insert editor linefeed)
• etc.

-- This code would be created upon clicking the button for that column, then the code would hopefully also be able to execute the created sub based on the initial click...or in some simple fashion. My also undiscovered idea was to determine the column from a substring of the button name and use it in a variable to plug into the code-writing scenario. Example static code sub to be used at each column through B through KB:

[VBA]Private Sub cmdToValues_B_Click()

On Error Resume Next
ActiveSheet.Unprotect
If Err.Number <> 0 Then
MsgBox "Password Declined —> ( Is your CAPS Lock On? )", vbInformation
Exit Sub
End If
On Error GoTo 0
Range("B10:B247").Copy
Range("B10:B247").PasteSpecial xlPasteValues
Application.CutCopyMode = False
ActiveSheet.Protect Password:="thelma"
Range("B9").Select
cmdToValues_B.BackColor = &H80000002

date_chg_prompt
End Sub
[/VBA]

Thanks Jonathan, didn't make it back to see your answer for a number of hours...

Although I didn't understand your answer, I went out and looked up just what a Class Module is, the most fundamental method for creating one, and some information on objects, methods, properties and arguments. Unfortunately, despite getting a very general idea of what was meant, I'm still in the fog.

Speaking just from the point of view where I currently sit with things, doing this seems more difficult learning curve-wise, and just as labor intensive as copy-pasting the previously posted sub and modifying each copy for its intended target/use.

At present, it seems I should just go ahead and use the zillions of separate subs...not the end of the world and should probably work just fine. One thing about doing it that way is that I have some basic idea of what's going on and can explain it a little to those who will use the pair of workbooks involved (only one of which uses these subs). I don't mind getting in over my head, but I have my limits.

Thanks for giving it a look.

Thanks Carim. I decided not to go ahead with enabling VB Project access. I'm unfamiliar with it and the folks receiving and using this workbook are very likely to try modifying things according to their own savvy which is formulas in cells. Just using VBA at all introduces a new aspect for them. I don't want to introduce too much at once.

Howeved, I can save Chip's page for times when I'm doing things for our own use. I've used his stuff before, years ago.

Thanks for the pointer. Consider this thread closed.