Solved: can't open specific Sheet from Closed Workbook, i get eror's! PLEASE HELP!?!

[PaSha]

Helo... i have a problem with VBA in excel...

ok the point is this... i was traying to make a buton, which detects some date infromation and apon them, when i click the buton some specific workbook would open and specific sheetfor 4 weeks in a month) and in every workbook i have 6 sheets(for 6 working days in a week) ... and when i use some if statements i can do that wehn i type only 3 or 4 od them, but when i type more of them i get Run time error '9' ... bla bla...i searched the web and google, and always the same answer,that maybe my sheet is wrong named,but it isn't i checked it 10 times and more... so i can paste you how this looks like ... and if someone nows where the problem is please tell me, HELP ME!!

Private Sub CommandButton1_Click()

a = Range("i12").Value
b = Range("i16").Value


If a = 1 And b = 1 Then Application.Workbooks.Open ("C:\Users\path\Documents\xxx\Ruckstand\RuckstandEintrag.xlsm")
Worksheets("PONEDELJEK-Motag").Select
If a = 1 And b = 2 Then Application.Workbooks.Open ("C:\Users\path\Documents\xxx\Ruckstand\RuckstandEintrag.xlsm")
Worksheets("TOREK-Dienstg").Select
If a = 1 And b = 3 Then Application.Workbooks.Open ("C:\Users\path\Documents\xxx\Ruckstand\RuckstandEintrag.xlsm")
Worksheets("SREDA-Mitwoch").Select
If a = 1 And b = 4 Then Application.Workbooks.Open ("C:\Users\path\Documents\xxx\Ruckstand\RuckstandEintrag.xlsm")
Worksheets("ČETRTEK-Donnerstag").Select
If a = 1 And b = 5 Then Application.Workbooks.Open ("C:\Users\path\Documents\xxx\Ruckstand\RuckstandEintrag.xlsm")
Worksheets("PETEK-Freitag").Select
If a = 1 And b = 6 Then Application.Workbooks.Open ("C:\Users\path\Documents\xxx\Ruckstand\RuckstandEintrag.xlsm")
Worksheets("SAMSTAG-Sobota").Select

If a = 2 And b = 1 Then Application.Workbooks.Open ("C:\Users\path\Documents\xxx\Ruckstand\RuckstandEintrag2.xlsm")
Worksheets("PONEDELJEK-Motag").Select
If a = 2 And b = 2 Then Application.Workbooks.Open ("C:\Users\path\Documents\xxx\Ruckstand\RuckstandEintrag2.xlsm")
Worksheets("TOREK-Dienstg").Select
If a = 2 And b = 3 Then Application.Workbooks.Open ("C:\Users\path\Documents\xxx\Ruckstand\RuckstandEintrag2.xlsm")
Worksheets("SREDA-Mitwoch").Select
If a = 2 And b = 4 Then Application.Workbooks.Open ("C:\Users\path\Documents\xxx\Ruckstand\RuckstandEintrag2.xlsm")
Worksheets("ČETRTEK-Donnerstag").Select
If a = 2 And b = 5 Then Application.Workbooks.Open ("C:\Users\path\Documents\xxx\Ruckstand\RuckstandEintrag2.xlsm")
Worksheets("PETEK-Freitag").Select
If a = 2 And b = 6 Then Application.Workbooks.Open ("C:\Users\path\Documents\xxx\Ruckstand\RuckstandEintrag2.xlsm")
Worksheets("SAMSTAG-Sobota").Select


'and so on for two more like this for week 3 and week4 ..


End Sub




___________

please help i need this for my job and i can't get it done for the hole day and more...

[XLGibbs]

[VBA]Private Sub CommandButton1_Click()
Dim a As Long, b As Long, wb As Workbook, ws As Worksheet
strPath As String, strSheetName As String


a = ThisWorkbook.Range("i12").Value
b = ThisWorkbook.Range("i16").Value

strPath = "C:\Users\path\Documents\xxx\Ruckstand\RuckstandEintrag" & a & ".xlsm"
strSheetName

Set wb = Workbooks.Open(strPath)

With wb
Select Case b
Case 1
strSheetName = "PONEDELJEK-Motag"
Case 2
strSheetName = "TOREK-Dienstg"
Case 3
strSheetName = "SREDA-Mitwoch"
Case 4
strSheetName = "CETRTEK-Donnerstag"
Case 5
strSheetName = "PETEK-Freitag"
Case 6
strSheetName = "SAMSTAG-Sobota"
End Select

.Sheets(strSheetName).Select
End With

End Sub[/VBA]

Might be something easier to handle in terms of editing.

Since the path only changes by adding 2, 3, 4 etc it can be built dynamically.

Since the sheet names repeat, you can just specify them once, as I have done.


The error was likely due to not specifying which workbook (explicity) to select the sheet. (Even though you just opened it, it may not be "active" workbook). If it wasn't the workbook in use..and the sheet doesn't exist in that book...Run time error...

HTH

[PaSha]

Hey man you're the best ...
thanks a lot for your help i managed to make this code and this button to work finally...
thanks thanks ...

now i can do something else, not just becouse of this stupid buton!!

[XLGibbs]

Hey man you're the best ...
thanks a lot for your help i managed to make this code and this button to work finally...
thanks thanks ...

now i can do something else, not just becouse of this stupid buton!!

My pleasure! You got that sorted out pretty quick, glad my suggestions helped out!

[PaSha]

hyXLGibbs...

hope i don't bother you or someone else, if this question was already posted, but i thought i can ask you, when you already managed to solve my question...

i have a similar button in another workbook... but this one has nothing important to do... just wehn user clicks on it, a value increases for 1...

but is there any posibility that i can block that buton for 5sec after the user clicks on it... so i could prevent a user makes a mistake and double kliks on it??

thanks again and hope i don't bother...

[XLGibbs]

Incidentally, if you made changes, feel free to post the final result so I and others can learn from it!

[Bob Phillips]

[vba]

Public Sub Test()
Static LastPress As Double

If Now > LastPress + TimeSerial(0, 0, 5) Then
LastPress = Now
Range("A1").Value = Range("A1").Value + 1
End If
End Sub
[/vba]

[PaSha]

Incidentally, if you made changes, feel free to post the final result so I and others can learn from it!

Actually i haven't made any big changes to that code, so it could work for me.... there where just some, adding needed for the path, becouse it was not complete... and i had to delete the .Sheet("nameSheet").Select from the code... becouse it was giving me some error when it was printed...

my final code looks now like this


[VBA]

Private Sub CommandButton1_Click()


Dim a As Long, b As Long, wb As Workbook, ws As Worksheet, strPath As String, strSheetName As String



a = Range("i12").Value
b = Range("i16").Value

strPath = "C:\Users\nameOfuser\Documents\nameofcompany\Ruckstand\RuckstandEintrag " & a & ".xlsm"

Set wb = Workbooks.Open(strPath)

With wb

Select Case b

Case 1
strSheetName = "PONEDELJEK-Motag"
Case 2
strSheetName = "TOREK-Dienstg"
Case 3
strSheetName = "SREDA-Mitwoch"
Case 4
strSheetName = "CETRTEK-Donnerstag"
Case 5
strSheetName = "PETEK-Freitag"
Case 6
strSheetName = "SAMSTAG-Sobota"

End Select

Sheets(strSheetName).Select

End With

End Sub

[/VBA]

[PaSha]

[vba]

Public Sub Test()
Static LastPress As Double

If Now > LastPress + TimeSerial(0, 0, 5) Then
LastPress = Now
Range("A1").Value = Range("A1").Value + 1
End If
End Sub
[/vba]

man this was a fast response ... thanks xld this really works

you people are the best ... really, best forum i ever saw...

i am new one at VBA programing, this way i'm asking probably so many and for someone stupid questions... but now i see how powerfull this stuff is, and it is GREAT ...

... xld ... i was traying now to manage to add some MsgBox "Tray again later" ... but it doesn't work good... i think becouse of the algorithm you wrote...maybe you know wehre in the code i can put that, so when user kliks and trays to do that again, it blocks him like already done with your code AND it would say to him: MsgBox "Tray again later" ...

my code for that button looks now like this :

[vba]
Private Sub CommandButton1_Click()

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

Static LastPress As Double

' MsgBox "Tray again later"

If Now > LastPress + TimeSerial(0, 0, 10) Then
LastPress = Now
' MsgBox "Tray again later"

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

End If

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

End Sub
[/vba]

[Bob Phillips]

Personally, I would find that MsgBox obtrusive and annoying

[vba]

Private Sub CommandButton1_Click()
Static LastPress As Double

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

If Now > LastPress + TimeSerial(0, 0, 10) Then
LastPress = Now
a = Range("i7").Value + 1
Range("i7").Value = a
Else
MsgBox "Try again later"
End If

Worksheets("PONEDELJEK-Montag").Protect Password:="MyPass"
End Sub
[/vba]

[PaSha]

Personally, I would find that MsgBox obtrusive and annoying

hy again xld and others...

i must thank you for your help showing me how to do this... it's working ...

i know it maight look like annoying but it's helpful at my case... but when someone else would use this code, think about if you really need this msgBox... maybe you do not...

so again thanks both of you ... you made my day look bether ....

you're the best and this forum

marked thread as Solved ...

[Bob Phillips]

I would find it annoying, because I have to take action to remove it, which is likely to take up the full 5 seconds, but would be darn annoying if it didn't and I got it again before I could increment.

I would persoanlly just throw a message in the statusbar, if they wonder why it didn't work, then would look around.

[PaSha]

Hey guys...

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:

[VBA]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[/VBA]

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 :

[VBA]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
[/VBA]

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:

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



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 but maybe for someone it isn't...

so i thank you already...