PDA

View Full Version : Solved: button problems... some extra wishes...



PaSha
11-20-2007, 01:06 PM
Hey guys...:think::think::think:

ahmm hope i don't bother to much ...

but i was traying to do something and i can't get it work without any error's ... so if someone could help me ...

this has also something to do with what xld said... about that prompt MsgBox when the user was traying to click on a button in some short interval...

i got some new task, which is a little bit more complicated... i had to do:

when a user was clicking on a button, the value of that cell would increase for 1... and now i would need this: when he clicks, that value increases, and a InputBox opens in which the user puts a specific code of that produkt which then goes in a special workbooks, sheet, and column... so i have managed that... you can see this here:

Private Sub CommandButton1_Click()
Worksheets("PONEDELJEK-Montag").Unprotect Password:="myPass"

Static LastPress As Double

If Now > LastPress + TimeSerial(0, 0, 7) Then
LastPress = Now

a = Range("i7").Value + 1
Range("i7").Value = a

Dim NextRow As Long
Dim Entry1 As String

' Determine next empty row

NextRow = Workbooks("ProduktNumern.xlsx").Sheets("Produkt numer").Columns("A").Cells(Rows.Count, 1).End(xlUp).Row + 1

' Prompt for the data
Entry1 = InputBox("Vpi?ite ?tevilko produkta")

' Write the data
Workbooks("ProduktNumern.xlsx").Sheets("Produkt numer").Columns("A").Cells(NextRow, 1) = Entry1


End If

Worksheets("PONEDELJEK-Montag").Protect Password:="myPas"

End Sub
this works great ...

but

what i need are the code for some special if stuff...

1. that the user can only type a 10 numeric nummber...when he types more or less the Input Box would prompt him...
Something code like this :

Sub GetValue3()
Dim Entry1 As Variant
Dim Msg As String
Msg = "Enter a 10 numeric value"
Do
UserEntry = InputBox(Msg)
If UserEntry = "" Then Exit Sub
If IsNumeric(UserEntry) Then
If UserEntry = 10 Then Exit Do
End If
Msg = "Your previous entry was INVALID."
Msg = Msg & vbNewLine
Msg = Msg & "Enter a 10 numeric value"
Loop

End Sub

but i can't get to work this when i put this code in the previous code, which already works fine...



2. also what i was traying to do is when a user clicks that button, and the value increases by 1 and a inputboxx come up to put some numer in... when the user diesn't types nothing and clicks ok... my value is still increased by 1 ... how can i do this back in that case??
and also when the user clicks on Cancel the same happens ??
i think this could be done something code like this one:

Sub GetValue2()
Dim Entry1 As Variant
UserEntry = InputBox("Enter the value")
If UserEntry <> "" Then (and so on )
End Sub



i know this is a hell about of code, i know how i could do this but i can't get this done, i neerprogramed in VBA i'm used to do that in c++ and so on...
this is my first week in VBA & excel...

so if someone yould help me ... i know this is :doh:but maybe for someone it isn't...

so i thank you already...

xld
11-20-2007, 01:39 PM
Private Sub CommandButton1_Click()
Static LastPress As Double

Worksheets("PONEDELJEK-Montag").Unprotect Password:="myPass"

If Now > LastPress + TimeSerial(0, 0, 7) Then
LastPress = Now

Range("i7").Value = Range("i7").Value + 1

Dim NextRow As Long
Dim Entry1 As String
Dim Msg As String

' Determine next empty row

NextRow = Workbooks("ProduktNumern.xlsx").Sheets("Produkt numer").Columns("A").Cells(Rows.Count, 1).End(xlUp).Row + 1

' Prompt for the data
Msg = "Vpi?ite ?tevilko produkta"
Do
Entry1 = InputBox(Msg)
If Entry1 = "" Then Exit Sub
If IsNumeric(Entry1) Then
If Len(Entry1) = 10 Then Exit Do
End If
Msg = "Your previous entry was INVALID." & vbNewLine & _
"Vpi?ite ?tevilko produkta"
Loop

' Write the data
Workbooks("ProduktNumern.xlsx").Sheets("Produkt numer").Columns("A").Cells(NextRow, 1) = Entry1
End If

Worksheets("PONEDELJEK-Montag").Protect Password:="myPas"

End Sub

PaSha
11-20-2007, 03:28 PM
hehe xld you're the best...

this is what i was traying to do... and now it works...

and i managed to make the code in such order that when the user click on the input box CANCLE or OK without typing any number in... the number in cell "i7" wouldn't increase by 1... which was the primary task ...

so thanks again xld and here is the finall code of that button if someone would need it ...

Private Sub CommandButton1_Click()
Worksheets("Sheet1").Unprotect Password:="myPass"

Static LastPress As Double

If Now > LastPress + TimeSerial(0, 0, 7) Then
LastPress = Now

Dim NextRow As Long
Dim Entry1 As String
Dim Msg As String

' Determine next empty row

NextRow = Workbooks("ProduktNumern.xlsx").Sheets("Produkt numer").Columns("A").Cells(Rows.Count, 1).End(xlUp).Row + 1

Msg = "enter the number of the product"
Do
Entry1 = InputBox(Msg)
If Entry1 = "" Then Exit Sub
If IsNumeric(Entry1) Then
If Len(Entry1) = 10 Then Exit Do
End If
Msg = "your last input was INVALID." & vbNewLine & _
"Type a 10. numeric number into the box."
Loop

' Write the data
Workbooks("ProduktNumern.xlsx").Sheets("Produkt numer").Columns("A").Cells(NextRow, 1) = Entry1

Range("i7").Value = Range("i7").Value + 1


End If

Worksheets("Sheet1").Protect Password:="myPass"

End Sub


:beerchug:

xld
11-20-2007, 03:30 PM
Great, I wasn't sure I had covered it all.

So are you in Bosnia or Herzegovina?

PaSha
11-20-2007, 03:54 PM
yes, but you were a lot of help for me... thank you again ... i wouldn't bother if it wouldn't be for the job...

yes, i'm actually a Bosnian, but right now i'm studying in Slovenia, becouse of the war which was in the year 92...

hehe so you know where Bosnia is, hehe cool??? You live in Chile ?? I thought some of people of big countries like Chile and others don't bother to think or to know where some little country in Europe is, hehe :)...

xld
11-20-2007, 04:25 PM
I am actually in a 'bigger' country than Chile. I fly the Chilean flag because I was over there when I joined VBAX, so I keep the flag for fun. I am English, and live in England, but yes, I know Bosnia. I have been to Ljubljana and Zagreb, Belgrade. I hitched through Yugoslavia (as it was then) as a student, down the old E70, so I would have just missed Bosnia. You must still feel a Bosnian in your heart. 15 years is a long time away, even somewhere as nice as Slovenia. Do you intend to go back?

PaSha
11-20-2007, 04:50 PM
I am actually in a 'bigger' country than Chile. I fly the Chilean flag because I was over there when I joined VBAX, so I keep the flag for fun. I am English, and live in England, but yes, I know Bosnia. I have been to Ljubljana and Zagreb, Belgrade. I hitched through Yugoslavia (as it was then) as a student, down the old E70, so I would have just missed Bosnia. You must still feel a Bosnian in your heart. 15 years is a long time away, even somewhere as nice as Slovenia. Do you intend to go back?

ooouh, ok now i understand your good english :cloud9:, hehe ...
and cool, you really know Bosnia,hehe,but how did it come that you were traveling through Yugoslavia back then ?? just for fun, tourism, school, job??
yes the good old times :cloud9: i actually don't remember about Yugoslavia nothing, i was born still in it, but to jung to remember how it was back then, but from the stories of my parents and others i hear only good stuff...all together all brothers...

and YES, tripple YES for that statement :friends: i just feel like a real Bosnian, which of course i'm am and gonna allways be...it's just in your hearth, you can't get it out. and actually i don't want it out :cloud9:.
And yes 15 years are a long time, but neither the war or the time has stoped us to go back. It was the year 97 when we first time went back to see the situation and then it was terrible, everything was gone, house, village, city, people, like a jungle... i was a little boy but i can remember... but after that we still were going back and little by little, holiday after holiday, we managed to build the house again, like many others and now it's just beautifull again... and we travel back every chance we get. actually this new year, i'm planning to spent there :cloud9:.

and yes slovenia is a beautifull country, which helped us, like many others, and till now i also got the slovenians papers, so it's cool. he studying is free and very nice... actually i was thinking next year to go to UK for exchange of students to study there for a semester or two... hope i'm gonna manage that somehow...

ou ****, sorry i wrote so much... but it's really nice to hear from someone who nows some things about us "little people" hehe... i apriciate that very much :cloud9:.

so are you planing to go onw more time for a tour through ex yugoslavia, this time you must see also Bosnia =) ... Sarajevo, Mostar, Visoko ( the new pyramid city :p ) and so on ...

xld
11-20-2007, 04:56 PM
Yeaj, it was just touring when I was a student.

I would love to go there, so many places to see, so many to want to see. My wife wants to go to Croatia, I would love to see the bridge at Mostar.

PaSha
11-20-2007, 05:15 PM
Yeaj, it was just touring when I was a student.

I would love to go there, so many places to see, so many to want to see. My wife wants to go to Croatia, I would love to see the bridge at Mostar.


hehe like me, i would love to go to UK... that's why i'm thinking about the exchange of students... if i don't do it know, later it's gonna be hader to find time and so on...

oh your wife certenly thinks about the sea in croatia :cloud9: hehe, yes it great :thumb ... and also montenegro has a beautiful sea site ...

we bosnians have only the city Neum at the sea, but that's why we have other benefits,hehe :cloud9:... i wanna also go on spend some days traveling throw Bosnia, here is many thing to see, which i also haven't :cloud9: ...

maybe we meet some where :thumb

PaSha
11-21-2007, 12:51 PM
hy people...
today i had some time and i worked on this project that i have... and i made some extra code on my sheets... if someone already read this i managed to make some functional button with help of some forum members... and i wanted to thank them and now i zhought when i already made some others i could paste that code if someone should need this in the future...

at first a made a button on which user when he clicks increases a number which represent a finished product and during the click a input box opens in which the user writes a 10numeric numer...and this numer goes in another sheet,in a special column, one cell after another...
but then i realised that a user could make a mistake typing that 10numeric numer and he wants to repare this ...so i made a extra button, which decreases the numer of finished products and deleted that previous numer which was inputed...

so the code for the second button looks like this now... :

Private Sub CommandButton10_Click()
Worksheets("PETEK-Freitag").Unprotect Password:="myPass"

Static LastPress As Double

If Now > LastPress + TimeSerial(0, 0, 5) Then
LastPress = Now

If Range("i8").Value = 0 Then Exit Sub
Dim NextRow As Long

NextRow = Workbooks("ProduktNumern.xlsx").Sheets("Produkt numer").Columns("B").Cells(Rows.Count, 1).End(xlUp).Row

Workbooks("ProduktNumern.xlsx").Sheets("Produkt numer").Columns("B").Cells(NextRow, 1).Delete

Range("i8").Value = Range("i8").Value - 1


End If


Worksheets("PETEK-Freitag").Protect Password:="myPass"
End Sub

so i hope his looks good, it works fine, but if someone has some other ideas... write it please down... i would be happy about that ...

i really begin to like this VBA code, :cloud9:

so hope this is gonna help someone in future... and one more time, thanks for the help of others..