Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! need some adjustments!
You have all been helpful in the past...but i need a little more!. Her
is some code i have sorted out, how do i get it to run on which eve sheet i switch to and only the one i switch to and if you read what have so far you will see a comment half way down advice on this as wel please! thanks, Simon. Option Explicit Dim myrange As Range Dim isect As Variant Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set myrange = Range("A1:D20") Set isect = Application.Intersect(myrange, Target) If isect Is Nothing Then MsgBox "Select Core, 1st, 2nd, 3rd or 4th Skill" & " or Enter Skill Competency Spot!" Exit Sub End If Dim t1 As String Dim I1 As Integer Dim I2 As Integer I1 = MsgBox("This Will Allow You To Enter a Skill, Continue?" 292, "Skills input") If I1 = 6 Then For I2 = 1 To 3 t1 = InputBox("name", "input", "") 'this next line i would like to get it to look up a range o another sheet and check for 1 'of the values entered in t1 If t1 = "mark" Or t1 = "paul" Or t1 = "eric" Or t1 = "john Then ActiveCell = t1 Exit Sub Else Worksheets("hidden").Visible = True Worksheets("hidden").Select ActiveSheet.Range("a2").Select Selection.Insert Shift:=xlDown Selection = t1 Worksheets("hidden").Visible = False End If Next 'I2 MsgBox "Please try again " & Chr(13) & "Skill " & t1 & " Entry no recognised " MsgBox "Please Contact Training Dept to Add Skill Title!!" End If End Sub p.s your quite welcome to have the test workbook e-mailed to u if yo want to play around with it just give me your addres -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! need some adjustments!
Change if from a worksheet event to a workbook event. Put the code in
Thisworkbook code module and replace Private Sub Worksheet_SelectionChange(ByVal Target As Range) with Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Look at Find in help for nthe other bit. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Simon Lloyd " wrote in message ... You have all been helpful in the past...but i need a little more!. Here is some code i have sorted out, how do i get it to run on which ever sheet i switch to and only the one i switch to and if you read what i have so far you will see a comment half way down advice on this as well please! thanks, Simon. Option Explicit Dim myrange As Range Dim isect As Variant Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set myrange = Range("A1:D20") Set isect = Application.Intersect(myrange, Target) If isect Is Nothing Then MsgBox "Select Core, 1st, 2nd, 3rd or 4th Skill" & " or Enter a Skill Competency Spot!" Exit Sub End If Dim t1 As String Dim I1 As Integer Dim I2 As Integer I1 = MsgBox("This Will Allow You To Enter a Skill, Continue?", 292, "Skills input") If I1 = 6 Then For I2 = 1 To 3 t1 = InputBox("name", "input", "") 'this next line i would like to get it to look up a range on another sheet and check for 1 'of the values entered in t1 If t1 = "mark" Or t1 = "paul" Or t1 = "eric" Or t1 = "john" Then ActiveCell = t1 Exit Sub Else Worksheets("hidden").Visible = True Worksheets("hidden").Select ActiveSheet.Range("a2").Select Selection.Insert Shift:=xlDown Selection = t1 Worksheets("hidden").Visible = False End If Next 'I2 MsgBox "Please try again " & Chr(13) & "Skill " & t1 & " Entry not recognised " MsgBox "Please Contact Training Dept to Add Skill Title!!" End If End Sub p.s your quite welcome to have the test workbook e-mailed to u if you want to play around with it just give me your address --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! need some adjustments!
Thanks Bob for your suggestion, i moved it but it didnt work at all, an
of course when i went back to macro''s to check it it wasnt there bu luckily it was when i opened VB editor! If anyone else has some suggestions i would be happy to recieve them. Simo -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot adjustments | Excel Discussion (Misc queries) | |||
Row Height Adjustments | Excel Discussion (Misc queries) | |||
time adjustments | Excel Discussion (Misc queries) | |||
Time Zone Adjustments | Excel Discussion (Misc queries) | |||
Adjustments | New Users to Excel |