How to: Enter a parameter to a macro while it's running!

Maybe this is not known by all guys here. Others may know better solutions for this (I as well, but I do not spread my entire knowledge here ;-)), but it may be new for some of you.

One benefit of a macro may be, to do the same processes on different values, e.g.: you have to create badges with different names . A very simple method is to do something like that:

Sub MkBadges()
...
Dim CurName As String
...
CurName = "Donald Duck"
...
End Sub

But with that you have to change the script in the VBA-Editor, if you need to create the next badge.

If you do it that way:

Sub MkBadges()
...
Dim CurName As String
...
CurName   =InputBox("Enter the name here:")
If CurName <> "" then
...
End If
End Sub

you get an input box where you can enter a name. You do not need to go to the VBA-Editor anymore and - for the case, that you started the wrong macro you can cancel it with entering  nothing.

A little more complicated is to read out the content of the ClipBoard. For this it would make sense, to create a standalone function which may replace later the InputBox.

As a pre-condition you have to check first, if your VBA-Project already have a reference to 'Microsoft Forms 2.0 ObjectLibrary'. Go for that in the menu in the VBA-Editor and open Options/References. Look, if this module is already checked, if not, search for it and check it.

Then you can copy more or less the following script in the same area where you have checked MS Forms.

Function GetTxtFromCB() As String

Dim MyData As DataObject, MyVar As Variant, FunctRet As String

FunctRet = "" ' set a default value

Set MyData = New DataObject
MyData.GetFromClipboard
On Error GoTo ErrHdl ' Do this for the case, that the content of the CB isn't a known object under Corel
MyVar = MyData.GetText
On Error GoTo 0 'after that you will receive the regular error messages

If VarType(MyVar) = vbString Then ' prevent processing of clipboard content which isn't a string
   FunctRet = MyVar
'The following lines are useful, but not always needed
   FunctRet = Replace(FunctRet, Chr(9), "") ' this removes all tabulators in the string
   FunctRet = Replace(FunctRet, Chr(10), "") ' this removes all Line Feeds in the string
   FunctRet = Replace(FunctRet, Chr(13), "") ' this removes all Carrige Return in the string
'   FunctRet = UCase(FunctRet) ' Use this, if you want to get only an upper case result
' ... more modifications on demand
   FunctRet = Trim(FunctRet) ' this replaces all leading and ending spaces from the string
'/The following lines are useful, but not always needed

End If

GetTxtFromCB = FunctRet 'Assigns either an empty string for the case, the clipboard isn't a string, otherwise a tidy string

Exit Function

ErrHdl:
MyVar = "" ' If the content of the CB isn't a known object, it will be become an empty string
Beep ' informs, that something unexpected happened
Resume Next

End Function

I have adapted it with some steps to avoid basic problems you may face, if you copy a string from e.g. an Excel-Worksheet or an HTML-Sheet. For sure, not all possible problems are covered with that, but you may adapt it for your specific needs. With a little more effort you even can adapt this script that you e.g.: paste a picture on a certain location and modify it's size.

As told before, there are many ways more to run a macro with different parameters. For example it's also possible to open Excel, fill a sheet with one or more rows containing different values and use this as input. For the example with the badges you may have one column with the name and one with the gender to print the text in blue or pink. Then you can execute your macro several times where you select different rows in the Excel-Sheet to control the input, but you can do this also for all (visible) rows at once. But to explain this without real example is a little difficult. Contact me, if you need some more information.