PDA

View Full Version : Solved: VBA Excel Question



LLL
02-15-2009, 11:02 AM
:dunno I am new to VBA. I have created userforms in excel. What I would like to do is have those forms appear only when clicked in a specific column. Also the form has checkboxes, and each cell will have different info. When the user returns to the specific cell ex:E5, I wan the form for that cell that they have checked boxes in to appear...Can anyone help me???

Tommy
02-15-2009, 12:01 PM
:hi: LLL,

Welcome to the forum!

I moved your question to a more appropriate area.

Enjoy! :thumb

LLL
02-15-2009, 12:06 PM
:hi: Thanks for your help. Where will I access the thread?

mdmackillop
02-15-2009, 12:43 PM
Welcome to VBAX
Can you post a sample of your workbook? Use Manage Attachments in the Go Advanced reply section.
Regards
MD

LLL
02-15-2009, 01:01 PM
For my initial question
I would like for Column F only ,to go to UserForm1 , and Column G only, to UserForm2.


A another item, I would like to do is this:
For Column H, plan is to have a dropbox for the following:
LLC/Partnership - (will go to UserForm3)
Corp - (will go to UserForm4)
Sole Prop - (will go to UserForm5)
LLC - ( will go to UserForm6)

Once a user has clicked on the form and checked boxes for a specified cell, that form only will appear upon reclicking in that cell


My userforms will be made of checkboxes, so they may select multiple items

Thanks for the help

mdmackillop
02-15-2009, 02:46 PM
I don't follow how your checkboxes relate to the spreadsheet
Put this code in the Worksheet module. I've made it a double-click event, but this can be changed if required.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Column
Case 6
Cancel = True
UserForm1.Show
Case 7
Cancel = True
UserForm2.Show
End Select
End Sub

LLL
02-15-2009, 03:29 PM
Okay, this works. On the userform there are checkboxes, if checks are placed in these boxes, upon return to that cell the form shows up with no checks? How do you get it to appear as last edited? Thanks so much for your help!

GTO
02-15-2009, 03:38 PM
How do you get it to appear as last edited?

Greetings LLL,

Just as to that one question, if you are asking how MD got the code to be layed out so nicely - when posting a code snippet, use the little green/white VBA button right above the message window you are typing in. Then plant the code between the tags.

Mark

mdmackillop
02-15-2009, 04:53 PM
This should show how to set checkboxes from sheet data and vice versa. It uses/sets dates in columns K-M

If you find the code is acting strangely (taking you to the Double_Click code), it's because of a bug I can't figure out!

GTO
02-15-2009, 05:12 PM
Hi Malcom,

I think you meant to have an IF or something to test if we're in a column we care about, before cancelling the double-click.

(Maybe? ...course I've been known to go down a wacky trail every now and then...)

Mark

mdmackillop
02-16-2009, 01:39 AM
Well caught. It should be moved within the Case statements.
thanks
Malcolm

GTO
02-16-2009, 04:09 AM
@mdmackillop:

Shucks brother, it is usually you catching me. I am most happy to be able to return the favor on the the rare occasion.

A great day to you and yours,

Mark

LLL
02-16-2009, 06:06 AM
Hi you guys! Thanks for your help:hi: TO be sure I am following you correctly, what i need to do is have IF statements under the Cases for the worksheet code? Is this correct? I will attach my original sheet with the first code. If you can provide an example under just one checkbox, I would greatly appreciate it. I want to be able to check a box, relative to that cell. And be able to go back to that cell and see the boxes I have checked.

mdmackillop
02-16-2009, 06:48 AM
Sorry, I just don't understand what you are trying to do with your checkboxes. Did you look at the attachment in Post #9?

LLL
02-16-2009, 07:10 AM
Hi, yes I did and thanks for getting back to me. Upon clicking for example in column F userform1 appears. Each cell in column F, this applies to. But each cell will have different items checked in the userform at different times. I want the checkboxes to each cell in column F to save the individual data. Does that make sense? Thanks again for all your help

mdmackillop
02-16-2009, 09:50 AM
I want the checkboxes to each cell in column F to save the individual data
I really have no idea what this means.

LLL
02-16-2009, 11:00 AM
Thanks for your patience, sorry. What I mean by that -I want the checkboxes to each cell in column F to save the individual data-

I click on the cell and the userform appears -
I click the checkboxes, so checkmarks appear -
I leave that cell -
Upon return to that cell, I would like to see what items I already checked off

Does that help?

mdmackillop
02-16-2009, 11:54 AM
You need to store the information in the workbook, not in the Userform. This example writes an x into corresponding cells.

LLL
02-16-2009, 02:28 PM
:bow: Thank you so very much, got it!!

mdmackillop
02-16-2009, 04:54 PM
Have a visit here (http://www.contextures.com/tiptech.html), a lot of useful examples

LLL
02-19-2009, 07:12 AM
Hi mdmackillop! Thanks for your previous help providing the sheet to me with formulas. I have UserForm1 complete and working fine. Do I utilize the same formulas for the remaining 5 userforms? For some reason I am running into a bug while trying to have the second form work. I will attach. Thanks

mdmackillop
02-19-2009, 11:41 AM
This is used for any userform regardless of name

Private Sub UserForm_Initialize()
You inserted a 2 which prevented the code from running.

If your code for the option buttons is to be basically the same, create another routine and pass the variables to it. I've shown this for Option 1 on UserForm1.


Private Sub CheckBox1_AfterUpdate()
AddX Me.CheckBox1, r.Row, 79
End Sub


Sub AddX(cb, r As Long, col As Long)
Dim t As Range
Set t = Cells(r, col)
If cb = True Then
If t = "" Then t = "x"
Else
t.ClearContents
End If
End Sub

LLL
02-19-2009, 12:10 PM
:hi: Thank you...Thank you