PDA

View Full Version : One UserForm, more Buttons which call that form - NOW problem..?



PaSha
12-12-2007, 06:22 AM
Helo guys...

i have a little problem...

i have had to make a userform ... in which some numbers are going to be filed in and then when clicking on OK button transported to another workbook and so on...

but the problem is this: i have many buttons on my sheet and when the user clicks that button the userform is beeing called... but i don't know how to make the code in the userform that the form knows which button called it?? becouse every button ... make something als with the code, actually the numbers are transferd in specific row for every button...

is there any posibility to make it so...that my form knows which button called it???

hope this is posible...

or do i have to make so many forms for every button ???

thanks in forward ...

Bob Phillips
12-12-2007, 06:31 AM
In the userform




Public Caller As String

Private Sub UserForm_Activate()
MsgBox Caller
End Sub


and in a code module



Public Sub ShowMyForm()
Dim myForm As UserForm1
Set myForm = New UserForm1
With myForm
.Caller = Application.Caller
.Show
End With
End Sub

PaSha
12-12-2007, 07:20 AM
hy xld, thanks for the replay...

i took your code, and it works and not,hehe... it opens a bsgbox when i click the button, but it is blank ... all white...

but hmmm, Actually i don't need the msgbox... what i need is that the form self knows which button was clicked so in there i can put some if statements...

for ex.
if button1 was clicked then

some code

end if

if button2 was clicked then
some other code
end if


is this posible ??

PaSha
12-12-2007, 07:54 AM
ok maybe i am not writing this clear enough...

so i'm gonna copy my code in the userform so you can see what i mean..

Private Sub potrdibutton_Click()
Dim NextRow As Long
Workbooks("ProduktNumern.xlsx").Sheets("Produkt numer").Activate
NextRow = Workbooks("ProduktNumern.xlsx").Sheets("Produkt numer").Columns("A").Cells(Rows.Count, 1).End(xlUp).Row + 1
Worksheets("Produkt numer").Unprotect Password:="myPass"
Workbooks("ProduktNumern.xlsx").Sheets("Produkt numer").Columns("A").Cells(NextRow, 1) = produktbox
Worksheets("Produkt numer").Protect Password:="myPass"

Workbooks("ProduktNumern.xlsx").Sheets("Typ numer").Activate
NextRow = Workbooks("ProduktNumern.xlsx").Sheets("Typ numer").Columns("A").Cells(Rows.Count, 1).End(xlUp).Row + 1
Worksheets("Typ numer").Unprotect Password:="myPass"
Workbooks("ProduktNumern.xlsx").Sheets("Typ numer").Columns("A").Cells(NextRow, 1) = typbox
Worksheets("Typ numer").Protect Password:="myPass"

Workbooks("ProduktNumern.xlsx").Sheets("Auftrags numer").Activate
NextRow = Workbooks("ProduktNumern.xlsx").Sheets("Auftrags numer").Columns("A").Cells(Rows.Count, 1).End(xlUp).Row + 1
Worksheets("Auftrags numer").Unprotect Password:="myPass"
Workbooks("ProduktNumern.xlsx").Sheets("Auftrags numer").Columns("A").Cells(NextRow, 1) = auftragsbox
Worksheets("Auftrags numer").Protect Password:="myPass"

Workbooks("RuckstandEintrag1.xlsm").Sheets("PONEDELJEK-Montag").Activate
Range("i7").Value = Range("i7").Value + 1
Unload UserForm1
End Sub

so if you look you can see what the form makes... the user writes in some numbers - there are under (produktbox, typbox, auftragsbox) ...
and there numbers go every in special worksheets (Produkt numer, Typ numer, Auftrags numer) ... and in special colums ... in this moment in the column ("A") ...

so what i wana do ... i have 9 buttons... and if the user clicks on the 1st button that code up is right... but if the user click the 2nd button the data should wnet to same sheets but column ("B") and so on...

so that's why i need to know in the userform which button was clicked... ??

PaSha
12-12-2007, 08:25 AM
it's ok - i took the school outdoor ..

i made 9 usefroms ... for each button one ...

but if anyone knows how to make it without making theese +8 userforms please replay ..

Bob Phillips
12-12-2007, 08:42 AM
I showed you how. The MsgBox was just to demonstrate which button called it, you test that value.

Norie
12-12-2007, 09:55 AM
Pasha

Why do you have multiple buttons?

What's their purpose?

PaSha
12-13-2007, 05:46 AM
hy again xld ... thanks and sorry, i realised what i had to do with the code you wrote... my mistake... at first didn' realised it...

thanks again...

i have one more question ... :cloud9:

in the code of the button i have UserForm1.Show

so the form shows up and executs it...

but after the i would plant this code: Range("i7").Value = Range("i7").Value +1

but this peace of code should only perform if in the userform the button ok was clicked...if it was cancle or exite then not...

how could i do this ??

hope i don't bother to much with this ..