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]

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

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 :hi: you're the best :yes:bow: ...
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 :banghead: because of this stupid button!!

[XLGibbs]

Hey man :hi: you're the best :yes:bow: ...
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 :banghead: becouse of this stupid buton!!

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

[PaSha]

hy XLGibbs...

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?? :think::think::think:

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]

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

[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, because it was not complete... and i had to delete the .Sheet("nameSheet").Select from the code... because it was giving me some error when it was printed...

my final code looks now like this



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

[PaSha]

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

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

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

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

... xld ... i was trying now to manage to add some MsgBox "Try again later" ... but it doesn't work good... i think because of the algorithm you wrote...maybe you know where in the code i can put that, so when user Clicks 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 :



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 "Try again later"
a = Range("i7").Value + 1
Range("i7").Value = a
End If
Worksheets("PONEDELJEK-Montag").Protect Password:="MyPass"
End Sub

[Bob Phillips]

Personally, I would find that MsgBox obtrusive and annoying



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

[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 :thumb...

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 :thumb

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:


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 never programmed in VBA i'm used to do that in c++ and so on...
this is my first week in VBA & excel...

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

so i thank you already...