Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help to Debug a line of code (2003 SP2)

    Good morning

    The following 2 subs work in Sheet1 of my workbook. I would now like to do the same in Sheet2 and thought the obvious answer was to copy the subs and then right click Sheet2's tab and paste it in there, however the code in Sheet2 now 'bugs' on the highlighted line. Any ideas please


    Private Sub MyCombo_Click()
    Dim varCombo As Variant 'User selection in Combo Box
    Dim nbrChars As Long 'Number of characters to extract
    Dim strCode As String 'Character extracted

    varCombo = ActiveSheet.MyCombo.Value

    nbrChars = InStr(1, varCombo, "-") - 1

    strCode = Trim(Left(varCombo, nbrChars))

    If strCode <> "Cancel" Then
    ActiveCell = strCode
    End If

    ActiveSheet.MyCombo.Value = ""
    ActiveSheet.MyCombo.Visible = False

    End Sub


    Private Sub Worksheet_BeforeDoubleClick _
    (ByVal Target As Range, Cancel As Boolean)

    If Not Intersect(Range("D12:N54"), Target) Is Nothing Then

    End If

    Cancel = True

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">ActiveSheet.MyCombo.Visible = True</span hi>

    End Sub

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help to Debug a line of code (2003 SP2)

    Have you copied the combo box named MyCombo to Sheet2?
    If not, ActiveSheet.MyCombo will fail on Sheet2 because there is no such control.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help to Debug a line of code (2003 SP2)

    Hi Hans

    Thanks for that, it is not but when I do copy and paste it it still does the same. The problem maybe that is a hidden combo which I will want to use eventually on several sheets, in your opinion would it be better to move this into the Workbook code rather than the Worksheet code and if so would this code need any modification

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Help to Debug a line of code (2003 SP2)

    Also - what is the point of your If...End If statement supposed to be?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help to Debug a line of code (2003 SP2)

    Make sure that the copy is also named MyCombo.

    The On Click event procedure for a control from the Control Toolbox needs to be in the worksheet module, you can't place it in the ThisWorkbook module.

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help to Debug a line of code (2003 SP2)

    Hi Rory

    It was from an earlier experimental piece of code, I did not realise I did not need it as it did not make the sub fall over ;-)

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help to Debug a line of code (2003 SP2)

    Thanks Hans

    I will go away and check it out

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help to Debug a line of code (2003 SP2)

    Hi Rory

    I presume that you mean the last End If. I deleted it b ut the sub would not work without it, put it back and it works, so i really don't know what it does but It does not work without it/

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help to Debug a line of code (2003 SP2)

    Perfect, Thanks Hans

    That now works in 5 pages of a sample workbook I was practising in, however in my 'live' WB which is about 5mb it is still bugging on that same line. I have a "Private Sub Worksheet_Change(ByVal Target As Range)" in each WS to conditionally format cells, do you think that this would conflict?

    Thanks

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help to Debug a line of code (2003 SP2)

    The last End If belongs to the line If Not Intersect(Range("D12:N54"), Target) Is Nothing Then above it. They don't do anything because there are no instructions in between them.
    If you delete the End If, you MUST also remove the line If Not Intersect(Range("D12:N54"), Target) Is Nothing Then.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help to Debug a line of code (2003 SP2)

    No, it means that the worksheet where the error occurs doesn't contain a combo box named MyCombo.

  12. #12
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help to Debug a line of code (2003 SP2)

    Thanks Hans

    I had an awfull feeling that you were going to say that <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I have another Macro that I insert into a WorkBook module, this creates a sheet and a combo (MyCombo) and this works perfectly in my trial WB. If when you have a moment you mind having a look at it I have attached a copy of that WB with all of the code in place and working in 5 sheets.

    However, when I copy and paste this Module and worksheet code into my existing WB it does not for some reason seem to make the Combo active. I have tried making a new Combo manually and calling it MyCombo but it still does not do anything.

    Thank you
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help to Debug a line of code (2003 SP2)

    Since the combo box is invisible, make sure that you haven't ended up creating multiple combo boxes named MyCombo on the same sheet by accident. That would no doubt confuse Excel.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •