PDA

View Full Version : Solved: Add a extra step to current macro button



keilah
05-15-2008, 11:20 AM
here is the link also; http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23405967.html



see attached workbook EE-modified-7K(2)
Hi

Need a additon to the current macro button in worksheet "Deal Selection" macro button Add Demand. If the user click this button they have the option to add another demand customer to the current list in column I (same worksheet)........and thats is it......

Would it be possible to also add the new demand customer say it was Petrobras......also to the bottom of the list in worksheet "Allocation (base) and (Vol) and (Sc.1) (Sc.2) and (Sc.3) as shown the example in worksheet "Allocation (Base)".....

on the click of the same macro button.....

R1C1
05-15-2008, 12:30 PM
You could use this formula to enter the value from the Add Demand form:

Sheets("Deal Selection").Range("I65536").End(xlUp).Offset(0,1).Value = TextBox1.Value
Sheets("Allocation (Base)").Range("C65536").End(xlUp).Offset(0,1).Value = TextBox1.Value
Sheets("Alloc (Sc.1)").Range("C65536").End(xlUp).Offset(0,1).Value = TextBox1.Value

Which will add the value from TextBox1 in Userform2 first empty cell below the last entry in the column.

Maybe this will help,

Alan

keilah
05-15-2008, 12:34 PM
Hi Alan.......

i am a beingner to VBA where would i add this could which module......????

Apologies for the silly question

keilah
05-15-2008, 01:33 PM
Alan

Just try it could not get it to work......

R1C1
05-15-2008, 05:26 PM
Sorry, sometimes I feel like I'm dyslexic :rotlaugh: the offset should have been (1,0) anyway. Here is some code to use for the CommandButton_Click code. Replace everything in there with this code and see if it does what you want.

Private Sub CommandButton1_Click()
UserForm2.Hide
Confirm = MsgBox("ARE YOU SURE YOU WANT TO ADD NEW CUSTOMER :" & TextBox1.Value, vbOKCancel, "ADDING CUSTOMER")
If Confirm = vbOK Then
Sheets("Deal Selection").Range("I65536").End(xlUp).Offset(1, 0).Value = TextBox1.Value
Sheets("Allocation (Base)").Range("C65536").End(xlUp).Offset(1, 0).Value = TextBox1.Value
Sheets("Alloc (Sc.1)").Range("C65536").End(xlUp).Offset(1, 0).Value = TextBox1.Value
Unload Me
Call rfs
Call ads
Call ads1
Call ads2
Call ads3
Call ads4
Else
MsgBox TextBox1.Value & " - CUSTOMER NOT ADDED", , "USER CANCEL"
End If
Unload Me
End Sub

Alan

R1C1
05-15-2008, 05:46 PM
I forgot to mention, the macro assigned to the Add Demand button on the Deal Selection sheet is pointing to a macro that opens form EE-Form. You need to right click that button and assign macro, and add a 2 to the end of the link so it will call OpenForm2, which contains the code to show UserForm2.

Alan

keilah
05-15-2008, 11:44 PM
THank Alan........for all you feedback and guidance....