VBA Excel Format Combobox

[Ascentient]

I am having a pathetically hard time formatting a Control Toolbox Combobox. I can get my list of values to display in it, but in order to see the values the FIRST time through I have to click on up or down "arrows", the kind you normally see in listboxes.

The second time I click on the combobox, it displays them in drop down format.

What am I doing wrong?

Ascent

 

[Zathras]

Sounds like you did not specify the LinkedCell property, or you are not putting a value into the cell you indicated.

 

[Ascentient]

Zathras,
I don't quite follow what you are saying. You are correct that the LinkedCell property is empty. I looked up LinkedCell for more information, but have not found anything useful. Could you provide me with some more insight?

Ascent

 

[Zathras]

Where are you putting the combo box, on a form or on a worksheet?

A little more information about what exactly you are trying to accomplish would help.

 

[Ascentient]

Zathras,
I figured out my issue with the combo box. The issue there was the result of the Font and Font Size for the contents of the combo box.

But to answer your question, I am putting it on a worksheet.

I do have another question though.

I have code that is validating the combobox it is as follows.

Private Sub cboFigure3_Change()
   Dim blnError As Boolean
   blnError = False
   blnError = CopyCells(cboFigure3.value)
   If blnError <> True Then
      PastCells (cboFigure3.Name)
   End If
   
End Sub

Private Function CopyCells(strValue As String) As Boolean
   
   Select Case strValue
      Case Is = "SQR1"
         Range("J66", "P68").Copy
      Case Is = "SQR2"
         Range("J69", "P71").Copy
      Case Is = "REC1"
         Range("J72", "P74").Copy
      Case Is = "REC2"
         Range("J75", "P77").Copy
      Case Is = "PAR1"
         Range("J78", "P80").Copy
      Case Is = "PAR2"
         Range("J81", "P83").Copy
      Case Is = "TRD1"
         Range("J84", "P86").Copy
      Case Is = "TRM1"
         Range("J87", "P89").Copy
      Case Is = "TRI1"
         Range("J90", "P92").Copy
      Case Is = "ZERO"
         Range("J93", "P95").Copy
      Case Else
         MsgBox "Incorrect selection. Please try again.", vbCritical, "Please Try Again."
         CopyCells = True
   End Select
   
End Function

After it Copies the designated cells, I want it to paste the content into the cells to the right of my combo box. This is the code I have, but I no it is not correct as it gives me the following error: Run-time error '1004': Method 'Range' of object' _Worksheet' failed

Private Sub PastCells(strFieldName As String)
   Select Case strFieldName
      Case Is = "cboFigure1"
         Range(J9, P11).PasteSpecial
      Case Is = "cboFigure2"
         Range(J12, P14).PasteSpecial
      Case Is = "cboFigure3"
         Range(J15, P17).PasteSpecial
      Case Is = "cboFigure4"
         Range(J18, P20).PasteSpecial
      Case Is = "cboFigure5"
         Range(J21, P23).PasteSpecial
      Case Is = "cboFigure6"
         Range(J24, P26).PasteSpecial
      Case Is = "cboFigure7"
         Range(J27, P29).PasteSpecial
      Case Is = "cboFigure8"
         Range(J30, P32).PasteSpecial
      Case Is = "cboFigure9"
         Range(J33, P35).PasteSpecial
      Case Is = "cboFigure10"
         Range(J36, P38).PasteSpecial
   End Select

End Sub

Any thoughts?
Ascent

 

[Zathras]

Try puting quotes around your cell references the same way you did in your CopyCells function.

 

[Ascentient]

Zathras,
That had mixed results but I ended up using the following code:

ActiveSheet.Paste Destination:=Worksheets("MASTER").Range("J9:P11")

Thanks for your help.
Ascent