Help with \

[rogersj]

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/27/2002 by Me
'


If ListBox1 = "Computer" Then

Range("D9").Select
Application.CutCopyMode = False
Selection.Copy
Range("C9").Select
ActiveSheet.Paste

ElseIf ListBox1 = "Monitor" Then

Range("D10").Select
Application.CutCopyMode = False
Selection.Copy
Range("C10").Select
ActiveSheet.Paste

End If

End Sub


Welp, If my list box has 'Computer' selected, and the Macro buttom is hit, it should copy the word Computer "D9" and paste it in "C9"

Im doing something wrong, please help me!
This message was edited by rogersj on 2002-08-27 17:33

 

[rogersj]

So Module1 should be this?:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/27/2002 by me
'


End Sub

Private Sub ListBox1_Click()
If ListBox1 = "Computer" Then
[C9] = ListBox1.Value
End If

If ListBox1 = "Monitor" Then
[C10] = ListBox1.Value
End If

End Sub

 

[Joe Was]

If you want a button or hot-key to work with your selection, then yes, if you want it to happen automatically if the listBox value is selected with a mouse click then put it in the ListBox1_Click event (Properties-View Code). JSW

 

[rogersj]

RAGE. I still cant get it to work... maybe its not called ListBox1, how could I find out?

 

[Joe Was]

Put the ListBox in Edit mode:

The ToolBar Icon: Triangle & T-Square or View-Toobars-Control Toolbox

Right click the ListBox to pull the dropdown menu, select "View-Code." This is where your code should go! It will also tell you the name of the listBox as the name of the Sub: Private Sub ListBox1_Click.

 

[rogersj]

I do that, then it takes me to Sheet1 Code View, which is blank.

 

[Joe Was]

That should take you to the click event for the control which will automatically be placed on the Sheet Module by Excel. This indicates to me that, You have not created the listBox correctly or you have a problem in Excel?

Drag a new listBox from the Control ToolBox, right click it, select properties, in the "FillRange" add:

D9:D10

where D9 has in it: Computer and D10 has Monitor.

Then exit the Editor. Right click the ListBox, select "View Code." copy the code to the Click event Sub, if it is on the page now. If no click event is on the page then you have a problem with Excel! Fix/repair and/or Re-Install. JSW

 

[Joe Was]

Also, re-click the Triangle&t Square Icon for the control editor before you try to run the code, to turn the edit mode off, so the code can run. JSW

 

[rogersj]

Ok, Day 2: It finally works!

In sheet 1 (view code) i have:

Private Sub ListBox1_Click()

If ListBox1 = "Computer" Then
[C9] = ListBox1.Value
End If

If ListBox1 = "Monitor" Then
[C10] = ListBox1.Value
End If

End Sub


Now, What If I wanted a button - macro to copy and paste the information... Now When i click computer or monitor in the list box, it pastes it correctly... So what would I do if I wanted to wait 'til I click the macro button before this would carry out its function?

 

[Juan Pablo González]

Don't put the code in the ListBox1_Click() event, put it in the CommandButton1_Click() event.

 

[Joe Was]

Just convert the Sub to Sheet Module code! As I indicated above. Like this:

Sub myListBox1()

If ListBox1 = "Computer" Then
[C9] = ListBox1.Value
End If

If ListBox1 = "Monitor" Then
[C10] = ListBox1.Value
End If

End Sub


The ListBox value will need to be highlighted with a selection click before you hit your Form Button to run the copy selection code above.

Also what were you doing wrong, now that the code works?