Pass Info From Userform to VBA Excel 2007

I can search this stuff, but always end up with specific code that I can't figure out how to alter. So... I guess I need some specific code.

I have 5 column headings; only one printer may be selected.
F1 = Receipt at Main
G1 = Remote 1
H1 = Remote 2
I1 = Remote 3
J1 = Remote 4

I have several values in Column A; either or both Food and Alcohol may be selected.
11 = Food
12 = Alcohol
13 = Alcohol
15 = Alcohol

I need a plain old x in the printer's column of any rows that contain the corresponding values in Column A.

Clear as mud?

If anyone finds this easier to do with the actual file, I'm happy to email it privately. I'm sorry I can't upload it here for confidentiality reasons.

Thanks so much in advance for your help!

LVL 22
Anne TroyEast Coast ManagerAsked:
Who is Participating?
Saqib Husain, SyedEngineerCommented:
Hi, All you had to do was get rid of the entire line which has .ClearConntents. There are two such lines one of which is already commented out.
Saqib Husain, SyedEngineerCommented:
You can find my email on my profile
Anne TroyEast Coast ManagerAuthor Commented:
Thank you. I have sent it.
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Roy CoxGroup Finance ManagerCommented:
You shoyuld remove any confidential data and post the workbook here
Saqib Husain, SyedEngineerCommented:
I found no file there
Saqib Husain, SyedEngineerCommented:
Try this code in the Userform1 module

Private Sub CommandButton1_Click()
Dim cel As Range
Dim ws As Worksheet
Set ws = Worksheets("EnterData")
For Each cel In ws.Range("A2:A" & Range("A2").End(xlDown).Row)
'cel.Offset(, 5).Resize(, 5).ClearContents
Select Case cel.Value
    Case 11
        If CheckBox1 Then markprinter cel
    Case 12, 13, 15
        If CheckBox2 Then markprinter cel
End Select
Next cel
End Sub
Sub markprinter(cel As Range)
cel.Offset(, 5).Resize(, 5).ClearContents
If OptionButton1 Then cel.Offset(, 6).Value = "x"
If OptionButton2 Then cel.Offset(, 7).Value = "x"
If OptionButton3 Then cel.Offset(, 8).Value = "x"
If OptionButton4 Then cel.Offset(, 9).Value = "x"
If OptionButton5 Then cel.Offset(, 5).Value = "x"
End Sub
Anne TroyEast Coast ManagerAuthor Commented:
Saqib, your code works near perfectly.  I tried to get rid of the .ClearContents, but I get an error then. I don't really want it to change anything that's already in that range F:J that might already exist.

Mr Wolfe: Thank you. It would have taken me hours to un-confidentialize (LOL) the workbook. Next time, I will just ask at some other site.
Roy CoxGroup Finance ManagerCommented:
I have run Forums as admin and moderated several Excel Forums and believe me this is Forum  exceptionally well run. All forums operate a similar requirement - all questions and answers should be in the public forums, otherwise pay someone to help you!
Anne TroyEast Coast ManagerAuthor Commented:
I did a couple years here myself, Roy, and created I know all about how it *should* be in a perfect world.
Anne TroyEast Coast ManagerAuthor Commented:
This worked perfectly for me. I did comment out that line while waiting for your response (to ensure I fixed it), but didn't think to try it without it.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.