VBA for prompt to fill in the certain cell

[Guest]

hello:

In lotus we can create a macro to ask us in the middle of the process to
copy to which range. The macro in lotus is like {?} then we sreach the cursor
downward the cell B for example, and then we can copy it to that range.

I want to make in excel similar to it, I want if we hit control D, iin cell
d5 , it will automatically Type "Finished dated 12 Jun 2007"" for today. To
morrow I will put in cell d20 the same word with the tomorrow 's date.

Can anybody teach me how to do it?, The wording of the VBA could be
"Finished" & To day()? or NOW()

Thanks in advance

Frank

 

[Guest]

Assign the shortcut CNTRL-d to the following macro:

Sub frank()
Dim s As String
s = "Finished dated "
ActiveCell.Value = s & Format(Now(), "dd mmm yyyy")
End Sub

 

[NickHK]

Frank,
Why is D5 today, but D20 for tomorrow ?
What about Wednesday ?

Range(??).value="Finished dated " & format (date, "dd mmm yyyy")

NickHK

 

[Guest]

Nick:

Wahtr I want to do that my clerck will just hit the macro, and it will type
those letters in D5. just for example that the copied range bottom left is in
E5, and tomorrow she will copy started from A6 to E20, so she will hit the
botton and it will type tommorows date. I just want to know it as a memo
which one to be updated later. Because the copepied range will be transfer to
another file.

I think Gary will give a light on this.

I would appreciate if you still have any idea

Frank

 

[Guest]

Thanks Gary for your smart idea. since this will be used for many excel
files, because each project will have a seperate file, Can we save the macro
in an excel file but can be run in any other project excel files when we open
it?

Thanks for your advice

Frank

 

[CoRrRan]

Thanks Gary for your smart idea. since this will be used for many excel
files, because each project will have a seperate file, Can we save the macro
in an excel file but can be run in any other project excel files when we open
it?

Thanks for your advice

Frank

You can do this by using an AddIn. Just create a clean Excel Workbook
and add the VBA code to it. Then save the workbook as an Excel AddIn and
publish this to the users who are going to use it. Let them install the
AddIn (Tools --> AddIns...) and everytime Excel starts, the AddIn will
be loaded and the user-defined functions and macro-procedures within it
are available for use.

HTH, CoRrRan

 

[Guest]

Thank you CoRrRan. I will try it.

Frank

You can do this by using an AddIn. Just create a clean Excel Workbook
and add the VBA code to it. Then save the workbook as an Excel AddIn and
publish this to the users who are going to use it. Let them install the
AddIn (Tools --> AddIns...) and everytime Excel starts, the AddIn will
be loaded and the user-defined functions and macro-procedures within it
are available for use.

HTH, CoRrRan

 

[Guest]

CoRrRan:

I need more clarifications on this:

1. when you said Add, is it thru view webcode on the clean sheet and we put
the VBA on it?
2. when you said published to user. is it to ask users to install when
users open an excel file, is OK?, or anytime user other files he/she has to
install it.

Thanks in Advance,

Frank

 

[CoRrRan]

CoRrRan:

I need more clarifications on this:

1. when you said Add, is it thru view webcode on the clean sheet and we put
the VBA on it?
2. when you said published to user. is it to ask users to install when
users open an excel file, is OK?, or anytime user other files he/she has to
install it.

Thanks in Advance,

Frank

1. "to add the code to your workbook" means that you open a new excel
document, then go to the Visual Basic Editor (ALT+F11), add a module to
your 'project' (which is the excel-file, usually called "VBAProject
(Book1)" for a new workbook in the English version of Excel). To add a
module to that project, click on "Insert --> Module". Then insert the
macro code into that module.

2. "to publish" means, you distribute the newly created Excel AddIn to
the users that are going to use it. The AddIn is a file with extension
..xla and can be e-mailed or put on a convenient network location where
the users can access it. They then have to install the AddIn through the
"Tools --> AddIns..." menu, by using the "Browse..."-button in that window.

For more background information, see:
http://www.cpearson.com/excel/CreateAddIn.htm

HTH, CoRrRan