excel passing value from combobox to another cell

[jazminecat]

I have a combobox on a page. I want the user to select a value from this cell, which will then be used to populate the data on the page when the user presses F9. i'm used ot doing this in access, not excel so i'm a bit lost.

How do I

a) capture the value the user selects
b) pass it to cell H9
and best case
c) then 'press' F9 to refresh the data on the page (it's a proprietary addin that uses excel, and f9 refreshes the data from cache)?

Here's what i have so far.

Private Sub ComboxBox1_Select()

ComboBox1.Select
If ComboBox1.Value = "1" Then
Range("H4").Select
ActiveCell.Value = "1"

ElseIf ComboBox1.Value = "2" Then
Range("H4").Select
ActiveCell.Value = "2"
End If


End Sub

 

[PHV]

Why not simply this ?
Range("H4").Value = ComboBox1.Value
Application.Calculate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazminecat]

well, it was a good idea, but I'm unable to implement it. I think I'm so going about this wrong. Here's my code for populating the box, but I seem to have to run it every time i open the app, which isn't what i want. Basically, the user selects the fiscal quarter from the dropdown and it should F9 - this isn't technically recalculate, as the data isn't formulae but is being pulled from the AS400 - so it's sort of like it's running a query. It's a proprietary plugin we purchased for excel that uses the F9 key to do this. So now I guess I have compounded the issue further. Here's my code for the box:

Private Sub ComboBox1_Change()


ComboBox1.AddItem "1"
ComboBox1.AddItem "2"
ComboBox1.AddItem "3"
ComboBox1.AddItem "4"
ComboBox1.AddItem "5"
ComboBox1.AddItem "6"
ComboBox1.AddItem "7"
ComboBox1.AddItem "8"
ComboBox1.AddItem "9"
ComboBox1.AddItem "10"
ComboBox1.AddItem "11"
ComboBox1.AddItem "12"

End Sub

Private Sub ComboxBox1_Select()

Range("H4").Value = ComboBox1.Value
Application.Calculate



End Sub

 

[bpeirson]

I have a form with a similar function. When the value in the combo box changes the value in another combo box changes as well.

I use the AfterUpdate method as below.

Private Sub ComboxBox1_AfterUpdate()

    ComboBox1.Select
    If ComboBox1.Value = "1" Then
    Range("H4").Select
    ActiveCell.Value = "1"
    
    ElseIf ComboBox1.Value = "2" Then
    Range("H4").Select
    ActiveCell.Value = "2"
    End If
    
    
End Sub

Any time the value in the combobox changes the code is run. "ComboBox1" in the private sub line has to be the name of the ComboBox to be monitored. I would rewrite your code as:

Private Sub ComboxBox1_AfterUpdate()

   
    Range("H4").Value = ComboBox1.Value
   
 
    
End Sub

I have never run a combBox directly in a sheet. All of mine are on UserForms.

Hope this helps.

 

[jazminecat]

That works! Now if I could just get it to actually calculate the sheet! Anyone know how to access the F9 key using VBA? Thanks for your help too!

 

[PHV]

Perhaps the SendKeys instruction ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bpeirson]

The help files say to add this line to your code to have Excel recalculate all cells.

Application.Calculate