excel vba code to create a button and assign script to it

[peace77]

Hello,

I am designing a project where I have to make a macro in excel. I have done most of it but now I am stuck at one piece and have no clue how to go about it.

There are two worksheets in the file: “Prefund” and “Settled Wire”

I have a macro that creates these two sheets and dumbs the data in it. Once the Prefund sheet is created the macro has to stop and the user has to see if column H has any values in it. If there is one then the user has to input the value in column I against the row that has a difference of non zero. For example cell H3 has a value (non zero) and hence the user sees it and then inputs the value in cell I3. (We are going to train the users to do so).

A B C D E F G H I
1 A 2 01 26 41079 41079 0
2 B 1 02 27 -21008 -20000 1008
3 C 1 03 28 12000 12000 0
4 D 1 04 29 10000 10000 0
5 E 1 05 30 13567 135675 0

Once the value is entered then the pertaining information is to be recorded in “Settled Wire” Sheet

To make this happen I have to create a button in “Prefund” Sheet. Once the user enters the value in column I he should hit this button and then another macro runs to record the pertaining data into “Settled Wire” sheet.

I was hoping if you could guide me with following things?

•How to write a code to create a button in “Prefund” sheet
•How to assign a script/code to this button- I can write the script but I don’t know how to assign this script to the code???

Any help is really appreciated.

Thanks a ton!

 

[ck1999]

Why use the user to input data already there?

Why not in you code loop through the cells in column H to determine if there is a value <> 0
like
if Range("H" & row).value <> 0 then
range("I" & row).value = Range("H"&row).value
end if

ck1999

 

[peace77]

Hi ck1999,

We do not the value that will go in coloumn I. The user has to put that in and then that data is to be recorded in another sheet.

In the above example the layout is not clear. Actually there are no values in coloumn I.

Peace 77

 

[ck1999]

Can you explain this better?

If there is one then the user has to input the value in column I against the row that has a difference of non zero. For example cell H3 has a value (non zero) and hence the user sees it and then inputs the value in cell I3. (We are going to train the users to do so)

What would the person enter in cell I3 in the case you stated above

ck1999

 

[peace77]

the user will enter the dates. but we dont know what the dates will be? its going to be dynamic

 

[ck1999]

ok a few more questions:

1. What version of excel?

2. Do you want a button actually on the sheet or a button on a toolbar ?

3. Could an inputbox work to capture the dates from the user?

ck1999

 

[peace77]

Hello CK1999,

Here are the answers to the questions

1. What version of excel?
Its Excel 2003

2. Do you want a button actually on the sheet or a button on a toolbar ?

Button on the sheet. Once the user enters the dates he ahs to click the button. And in the background I have to assign a procedure/function to that button so that the data is automatically recorded into the second worksheet
. In other words I have assign a macro to this button

3. Could an inputbox work to capture the dates from the user?

I do not know much of programming and/or VBA. I am not sure if input box can help resolve this issue. If you suggest that it can resolve it then i can used inout box instead of button. But I dont know how to do that as well.

Thanks,

Peace

 

[ck1999]

The code for the input box would be to add this to the end of your insert code into

after your code you have that created the prefund sheet add

dim vrow
for for vrow= 1 to range("h1").end(xldown).row 'if your data starts in row1
if Range("H" & vrow).value <> 0 then
range("I" & row).value = inputbox("Enter the Dates","User Input")
end if
next vrow

then execute your copy code to the other sheet.
ck1999

 

[electricpete]

I'm not sure I follow the whole question. Going to the issue of inserting a button:

#1 - Do you know how to insert a button into a spreadsheet? View / Toolbars / Control Toolbox. Select commandbutton from the boolbar. Drag/drop to insert button. Right-click / properties - change the caption and wordwrap as desired. Double click to open vba editor which will be in edit mode on the associated sub.. something like Private Sub CommandButton1_Click() End Sub. Enter your code. Go back to excel. Turn off design-mode (the left-most button on the control toolbar. Click button and your code will execute.

#2 - If the button must appear AFTER you create a sheet using vba code, then you should create another sheet (hidden if desired) first, then your vba can copy that sheet. When it is copied, the button and code will be copied along with it.