This may not be exactly what you are looking for, but hopefully it will help. The code will change the color of the cell based on which animal is selected from the combobox.
Tim
Here is the setup:
Cell A1 through B9 are this(the animal name in column A, and the type of animal in column B)
Fluffy cat
Rex dog
Tiger dog
Spike dog
Garfield cat
Tom cat
Chester dog
Prissy cat
Boo cat
Cell H1 through H3 have these values:
dog
cat
bear
I added a combo box with the default name of combobox1
In WorksheetOpen, I added this:
Private Sub Workbook_Open()
Sheet1.ComboBox1.Clear
Sheet1.ComboBox1.List = Sheet1.Range("H1:H3").Value
End Sub
In Sheet1, I added this:
Private Sub ComboBox1_Change()
Dim ComboBoxSelection
Dim RowNumber As Integer
Dim RowNumberString As String
ComboBoxSelection = ComboBox1.Text
For RowNumber = 1 To 9
RowNumberString = LTrim(Str(RowNumber))
'If the data in column B does NOT match the combo box selection, then black the cells
If Range("B" & RowNumberString).Text <> ComboBoxSelection Then
Range("A" & RowNumberString & ":" & "B" & RowNumberString).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Else
'The data in column B does matches the combo box selection, so make those cell backgrounds white
Range("A" & RowNumberString & ":" & "B" & RowNumberString).Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Next
'Get the selection away from the data cells
Range("D5").Select
End Sub
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf Of Tayyab
Sent: Wednesday, June 05, 2013 3:37 PM
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] auto run macro when link formula change
no I make a macro to hide columns, it works perfectly when I enter or change sheet, but I make a Combox(form control) to link formula. My requirement is when combox selection change run the macro. It not worked.
________________________________
From: Alif Be <alif98@gmail.com<mailto:alif98%40gmail.com>>
To: ExcelVBA@yahoogroups.com<mailto:ExcelVBA%40yahoogroups.com>
Sent: Monday, June 3, 2013 1:53 PM
Subject: Re: [ExcelVBA] auto run macro when link formula change
I think you can assign macro for combobox form control. Just right click on
the control and choose Assign Macro.
Best regards,
Alif Be
On Sat, Jun 1, 2013 at 1:35 AM, David Smart
<smartware.consulting@gmail.com<mailto:smartware.consulting%40gmail.com>>wrote:
> **
>
>
> You don't say whether it's a form control or an Active X control.
>
> For a form control, you might be stuck.
>
> For an Active X control, you can use the ComboBox_Change event to pick up
> changes made via the combo box. This will also fire if you change the
> linked cell directly, because that changes the combo box value..
>
> Regards, Dave S
>
>
> ----- Original Message -----
> From: "Tayyab" <sheikhtayyab@yahoo.com<mailto:sheikhtayyab%40yahoo.com>>
> To: <ExcelVBA@yahoogroups.com<mailto:ExcelVBA%40yahoogroups.com>>
> Sent: Friday, May 31, 2013 10:50 PM
> Subject: [ExcelVBA] auto run macro when link formula change
>
> hi
> I have a sheet that have a combo box selection menu to select code, when
> code is selected sheet auto changes value. then also have a macro that I
> want to run . macro only run when I change any cell value, but not run
> when
> changing the combo option.
> I make attach code. Please help
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim KeyCells As Range
>
> Set KeyCells = Range("g6:i10") 'combo box link cell
>
> If Not Application.Intersect(KeyCells, Range(Target.Address)) _
> Is Nothing Then
>
> Call HideColumns 'macro call that to hided black columns
>
> End If
> End Sub
>
> Thanks & regards
>
> Tayyab
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> the
> ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com<http://www.avg.com>
> Version: 10.0.1432 / Virus Database: 3184/5871 - Release Date: 05/31/13
>
>
>
--
Best regards,
Alif Be
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (7) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar