Guidance required please...

  • Thread starter Thread starter Vacuum Sealed
  • Start date Start date
V

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
 
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
 
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.
 
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
 
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
Click to expand...

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
 
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.
 
Typo!

This is all you'd need if 'Run Setup'!B2 is a DV dropdown containing
only valid sheet names.
 
Claus Busch submitted this idea :
Hi Garry,

why Select Case?
Click to expand...

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...
 
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
 
Hi Garry

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

Sheets(sVal).Select

Mick.
 
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.
 
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..
 
Vacuum Sealed explained on 6/18/2011 :
Hi Garry

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

Sheets(sVal).Select

Mick.
Click to expand...

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
 
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.
 
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.
 
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.
 
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.
Click to expand...

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.
 
Thx Garry

Guilty as charged...

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

1 less problem to stress over.....

:-/

Cheers
Mick.
 
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.
 
Back
Top