asked on

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!

Saqib Husain

You can find my email on my profile

Anne Troy

ASKER

Thank you. I have sent it.

Roy Cox

You shoyuld remove any confidential data and post the workbook here

Saqib Husain

I found no file there

Saqib Husain

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 Troy

ASKER

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.

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

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 Troy

ASKER

I did a couple years here myself, Roy, and created vbaexpress.com. I know all about how it *should* be in a perfect world.

Anne Troy

ASKER

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.