Guidance required please...

[Vacuum Sealed]

Hi All

I have this exact code structure working fine in another WB, same system,
same (Almost) everything.

Difference in this version is that the value in Cell [B2] is from a
DataValidated dropbox...

Will I need to do something different this time around.

Sub My_AutoMagic_Btn()

Dim aCell As Range
Sheets("Run Setup").Select
Set aCell = [B2]

If Not aCell.Value Is Nothing Then
Select Case True
Case aCell.Value = "Bray"
Sheets("Bray").Select

Case aCell.Value = "Burn"
Sheets("Burn").Select

Case aCell.Value = "Cool"
Sheets("Cool").Select

Case aCell.Value = "Morn"
Sheets("Morn").Select

Case aCell.Value = "Oak"
Sheets("Oak").Select

Case aCell.Value = "Pres"
Sheets("Pres").Select
End Select
End If

TIA
Mick

 

[Claus Busch]

Hi Mick,

why not a worksheet_change event?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$B$2" Then Exit Sub

With Target
If .Value <> "" Then
Application.Goto Sheets(.Value).Range("A1")
End If
End With

End Sub



Regards
Claus Busch

 

[Vacuum Sealed]

Hi Claus

Thx for your reply

the code hangs on this line:

Application.Goto Sheets(.Value).Range("A1")


I appreciate your efforts, though it isn't quite what I was looking for as I
don't need the focus changed to the destination sheet once the user selects
a value from the dropdown....

Once he/she has filled in all the other relavent cells, then they hit a
CmdBtn Then I would like it to select the appropriate Sheet based on said
Value, then do it's thing.

As I stated, I have it working very well already in another workbook,
although that is triggered by a standard cell, not a dropdown..

Thx again

Mick.

 

[GS]

Try...

Sub My_AutoMagic_Btn()
Dim sVal As String
sVal = Sheets("Run Setup").Range("B2").Value
If sVal = "" Then Exit Sub
Select Case sVal
Case "Bray": Sheets("Bray").Select
Case "Burn": Sheets("Burn").Select
Case "Cool": Sheets("Cool").Select
Case "Morn": Sheets("Morn").Select
Case "Oak": Sheets("Oak").Select
Case "Pres": Sheets("Pres").Select
End Select
End If

 

[Claus Busch]

Hi Garry,

Am Sat, 18 Jun 2011 11:27:40 -0400 schrieb GS:

Sub My_AutoMagic_Btn()
Dim sVal As String
sVal = Sheets("Run Setup").Range("B2").Value
If sVal = "" Then Exit Sub
Select Case sVal
Case "Bray": Sheets("Bray").Select
Case "Burn": Sheets("Burn").Select
Case "Cool": Sheets("Cool").Select
Case "Morn": Sheets("Morn").Select
Case "Oak": Sheets("Oak").Select
Case "Pres": Sheets("Pres").Select
End Select
End If

why Select Case?

Sub My_AutoMagic_Btn()
With Sheets("Run Setup").[B2]
On Error Resume Next
If Not Sheets(.Value) Is Nothing Then
Sheets(.Value).Select
End If
End With
End Sub


Regards
Claus Busch

 

[GS]

Shorter version...

Sub My_AutoMagic_Btn2()
Dim sVal As String
sVal = Sheets("Run Setup").Range("B2").Value
If Not sVal = "" Then Sheets(sVal).Select
End If

This is all you'd need if Run 'Setup'!B2 is a DV dropdown containing
only valid sheet names.

 

[GS]

Typo!

This is all you'd need if 'Run Setup'!B2 is a DV dropdown containing
only valid sheet names.

 

[GS]

Claus Busch submitted this idea :

Hi Garry,

why Select Case?

Already answered, Claus. (Looks like timing is off for our postings) I
assume Mick wanted the Select Case for his own purposes, however I
wouldn't use it in this scenario. My 2nd post is how I'd do this...

 

[Gord Dibben]

Should work no matter how the value is inputted to B2 although I would re-write
a bit.

Sub My_AutoMagic_Btn()

Dim aCell As Range
Sheets("Run Setup").Select
Set aCell = [B2]

If Not aCell Is Nothing Then
Select Case aCell.Value
Case Is = "Bray"
Sheets("Bray").Select

Case Is = "Burn"
Sheets("Burn").Select

End Select
End If
End Sub


Gord Dibben MS Excel MVP

 

[Vacuum Sealed]

Hi Garry

Thank you for your input, the Code halted at this point.

Sheets(sVal).Select

Mick.

 

[Vacuum Sealed]

Hi Gord

Thx for your input also, although I have to say, this is the most bizaar
thing.

The code I originally posted works perfectly on my other file, yet this
seems to be a real nut-buster.

I even went so far as to convert the cell back to standard and it is still
not wanting to play fair....

Your rework hasn't garnered a positive outcome....

Cheers
Mick.

 

[Vacuum Sealed]

Hi Guy's

Tried an different approach and used Numeric Values as the names for the
sheets eg 1234, 2345....etc....

Blow me down both Garry's and Gord's code works perfect..

I'm not going to pretend I understand why, just grateful it's another hurdle
out of the way and I can move forward onto the next brickwall that pops up.

Thx heaps again to all...

Appreciate the assist...

Mick..

 

[GS]

Vacuum Sealed explained on 6/18/2011 :

Hi Garry

Thank you for your input, the Code halted at this point.

Sheets(sVal).Select

Mick.

Mick,
There's another typo! The code should read...

Sub My_AutoMagic_Btn2()
Dim sVal As String
sVal = Sheets("Run Setup").Range("B2").Value
If Not sVal = "" Then Sheets(sVal).Select
End Sub '<<==NOT End If

 

[Vacuum Sealed]

All good Garry

this works perfect, although, as I comments, I changed the original Values
from Alpha to Numeric and it works like a charm.

Sub Go_Store()

Dim sVal As String

With Application
.ScreenUpdating = False
End With

sVal = Sheets("Run Setup").Range("B2").Value

If Not sVal = "" Then

Sheets(sVal).Select

End If

ActiveSheet.Select

Columns("B").Find("", Cells(Rows.Count, "B"), xlValues, _
xlWhole, , xlNext).Select

ActiveCell.Select
With Selection
.Value = Sheets("Run Setup").Range("D2").Value
End With

ActiveCell.Offset(0, 1).Select
With Selection
.Value = Sheets("Run Setup").Range("E2").Value
End With

ActiveCell.Offset(0, 1).Select
With Selection
.Value = Sheets("Run Setup").Range("F2").Value
End With

With Application
.ScreenUpdating = True
End With

End Sub

Thx again
Mick.

 

[Vacuum Sealed]

Garry

I took onboard what you mention and I did the following.

Changed Cell to TEXT format
Changed the sheet back to text name "Bray"

Again, it hung on the sVal=.

So I undid all the changes again to reflect the numeric state and it worked.

Got me stumped, I even went to check if I had a "Missing" highlighted
reference libraries and I only have 2 in use and they are fine, 1 = Visual
Basic for Applications, the other being MS Excel 12.0 Object Library, with
this in mind, any thoughts spring to mind whereby you think I may need to
expand on my Library to add addition features that are possibly needed.

BTW: Although, at home I use 2007, this being for work, they use 2003.

Thx again
Mick.

 

[isabelle]

hi,

If Not [B2] Is Nothing Then Sheets("" & [B2]).Activate

 

[Vacuum Sealed]

Thx for replying Isabelle

This also hung

Sub Go_Store2()
Dim aCell As Range

With Application
.ScreenUpdating = False
End With

Sheets("Run Setup").Select
Set aCell = [B2]

If Not [B2] Is Nothing Then Sheets("" & [B2]).Activate
End Sub

This section ( " Sheets("" & [B2]).Activate ") was highlighted after I
changed the sheets back the Alpha's, yet it worked perfectly well when I had
the sheet name as a Numeric..

Still got my stumped...

Thx again.
Mick.

 

[GS]

Vacuum Sealed used his keyboard to write :

Garry

I took onboard what you mention and I did the following.

Changed Cell to TEXT format
Changed the sheet back to text name "Bray"

Again, it hung on the sVal=.

So I undid all the changes again to reflect the numeric state and it worked.

Got me stumped, I even went to check if I had a "Missing" highlighted
reference libraries and I only have 2 in use and they are fine, 1 = Visual
Basic for Applications, the other being MS Excel 12.0 Object Library, with
this in mind, any thoughts spring to mind whereby you think I may need to
expand on my Library to add addition features that are possibly needed.

BTW: Although, at home I use 2007, this being for work, they use 2003.

Thx again
Mick.

Mick
The tests I did were xl10 and xl12, so I had both earlier and current
versions covered. Your text names worked fine in both. What I suspect
may be happening is the text "Bray" may be entered with a trailing
space which you can't see. I find it a typical syndrome that people
(for some unexplained reason) often type a trailing space after each
word without being conscious about doing so. Also, I make it a personal
naming convention to never use spaces in sheetnames (or filenames) just
to avoid 'double quote' issues that may arise for various reasons. Not
saying that has anything to do with your issue, though.

 

[Vacuum Sealed]

Thx Garry

Guilty as charged...

Chalk up another "Idiot of Assumption"....

1 less problem to stress over.....

:-/

Cheers
Mick.

 

[Vacuum Sealed]

My humble apologies to all,

Garry (GS) raised the question as to whether or not I had trailing spaces,
it turned out I did, hence the anomoly...

I sincerely appreciate eberyone patients and persistance in helping.

Thx again.

Mick.