Need help with this Visual Code, excel

Status
Not open for further replies.

Student1000

Board Regular
Joined
Jul 17, 2007
Messages
58
Well, couple of days ago someone responded to the question i had on how to increase the font size of a data validation(dropdownbox) and he gave me the code below. This code was to eliminate double clicking on a cell to increase its font.
I created a combo box and add the code in visual basic. However, it does not seem to work. I dont know if i need to do something else or doing something wrong.
Am still working on it, so if there is any body that can help me out i would appriciate it alot.
thank you


Code:
Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim str    As String 
    Dim cboTemp As OLEObject 
    Dim ws     As Worksheet 
    Set ws = ActiveSheet 

    If Target.Validation.Type = xlValidateList Then 
        Set ws = ActiveSheet 
        Cancel = True 
        Set cboTemp = ws.OLEObjects("TempCombo") 
        On Error Resume Next 
        With cboTemp 
            .ListFillRange = "" 
            .LinkedCell = "" 
            .Visible = False 
        End With 
        On Error GoTo errHandler 
        Application.EnableEvents = False 
        str = Target.Validation.Formula1 
        str = Right(str, Len(str) - 1) 

        With cboTemp 
            .Visible = True 
            .Left = Target.Left 
            .Top = Target.Top 
            .Width = Target.Width + 15 
            .Height = Target.Height + 5 
            .ListFillRange = ws.Range(str).Address 
            .LinkedCell = Target.Address 
        End With 
        cboTemp.Activate 
    End If 

errHandler: 
    Application.EnableEvents = True 
    Exit Sub 

End Sub

EDIT: added code tags - Moderator
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Where are you putting that code?

it should go on the worksheet module, not a standard module.

right click the sheet tab you want to apply this to, view code
paste it there
 
Status
Not open for further replies.

Forum statistics

Threads
1,181,649
Messages
5,931,215
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top