none
Populating UserForm from ActiveCell

    Question

  • Hello, all! I'm having some trouble with trying to send data from an ActiveCell back to a UserForm. I know how to do it one way, but can seem to figure it out the other. I'd like the user to have the ability to edit by clicking an ActiveCell, having the UserForm come up, and recalling the information that was entered into the ActiveCell. I know how to accomplish this with straight text, but have no idea how to do it with drop downs and option buttons. Any help would be appreciated. Thanks.

    Private Sub UserForm_Activate()
    'Position top/left of Excel App
    	Me.Top = Application.Top
    	Me.Left = Application.Left
    	
    'Approx over top/left cell (depends on toolbars visible)
    	Me.Top = Application.Top + 250
    	Me.Left = Application.Left + 500
    End Sub
    
    Private Sub UserForm_Initialize()
    	OptionButton1.value = True
    	
    	With cbxMM
    		.AddItem "MM"
    		.AddItem "01"
    		.AddItem "02"
    		.AddItem "03"
    		.AddItem "04"
    		.AddItem "05"
    		.AddItem "06"
    		.AddItem "07"
    		.AddItem "08"
    		.AddItem "09"
    		.AddItem "10"
    		.AddItem "11"
    		.AddItem "12"
    	End With
    	
    	With cbxDD
    		.AddItem "DD"
    		.AddItem "01"
    		.AddItem "02"
    		.AddItem "03"
    		.AddItem "04"
    		.AddItem "05"
    		.AddItem "06"
    		.AddItem "07"
    		.AddItem "08"
    		.AddItem "09"
    		.AddItem "10"
    		.AddItem "11"
    		.AddItem "12"
    		.AddItem "13"
    		.AddItem "14"
    		.AddItem "15"
    		.AddItem "16"
    		.AddItem "17"
    		.AddItem "18"
    		.AddItem "19"
    		.AddItem "20"
    		.AddItem "21"
    		.AddItem "22"
    		.AddItem "23"
    		.AddItem "24"
    		.AddItem "25"
    		.AddItem "26"
    		.AddItem "27"
    		.AddItem "28"
    		.AddItem "29"
    		.AddItem "30"
    		.AddItem "31"
    	End With
    	
    	Dim m As Object
    	If ActiveCell.value = vbNullString Then Exit Sub
    	With CreateObject("VBScript.RegExp")
    		.Global = True
    		For Each m In .Execute(ActiveCell.value)
    			If cbxDD = "14" Then
    				Me.cbxDD.AddItem cbxDD
    			End If
    		Next
    	End With
    	
    	
    End Sub
    Private Sub btnOK_Click()
    	If cbxDD.value = "DD" And cbxMM.value = "MM" Then
    		MsgBox "Please enter a month and date."
    		Exit Sub
    	End If
    	If cbxDD.value = "DD" Then
    		MsgBox "Please enter a date."
    		Exit Sub
    	End If
    	If cbxMM.value = "MM" Then
    		MsgBox "Please enter a month."
    		Exit Sub
    	End If
    	If cbxMM.value = "MM" Then
    		MsgBox "Please enter a month."
    		Exit Sub
    	End If
    	If OptionButton1.value = True Then
    		ActiveCell.value = "x" + cbxMM.value + "/" + cbxDD.value + " " + txtCode + " " + ChrW(8730)
    		Unload Me
    		UserForm1.Hide
    	End If
    	If OptionButton2.value = True Then
    		ActiveCell.value = "x" + cbxMM.value + "/" + cbxDD.value + " " + txtCode
    		Unload Me
    		UserForm1.Hide
    	End If
    	If OptionButton3.value = True Then
    		ActiveCell.value = cbxMM.value + "/" + cbxDD.value + "- " + txtCode
    		Unload Me
    		UserForm1.Hide
    	End If
    	If OptionButton4.value = True Then
    		ActiveCell.value = "x" + cbxMM.value + "/" + cbxDD.value + " " + txtCode + " DR"
    		Unload Me
    		UserForm1.Hide
    	End If
    	If OptionButton5.value = True Then
    		ActiveCell.value = "x" + cbxMM.value + "/" + cbxDD.value + " " + txtCode + " C"
    		Unload Me
    		UserForm1.Hide
    	End If
    	If OptionButton6.value = True Then
    		ActiveCell.value = cbxMM.value + "/" + cbxDD.value + "- CP"
    		Unload Me
    		UserForm1.Hide
    	End If
    	If OptionButton7.value = True Then
    		ActiveCell.value = cbxMM.value + "/" + cbxDD.value + " " + txtCode + " Cancelled"
    		Unload Me
    		UserForm1.Hide
    	End If
    	If OptionButton8.value = True Then
    		ActiveCell.value = "Lost"
    		Unload Me
    		UserForm1.Hide
    	End If
    End Sub
    
    Private Sub btnCancel_Click()
    	Unload Me
    End Sub


    • Edited by Lanmanna Friday, March 17, 2017 2:34 PM
    Friday, March 17, 2017 2:31 PM

Answers

  • Try the example workbooks at the following link. (Two workbooks in the zipped file).

    https://1drv.ms/u/s!ArAXPS2RpafCjG5h0gJ61qOGSIsK

    Workbook SAMPLE_A is a simple code edit to your example. However, I have used loops to add the lists to the months and days and have changed the code to use a label to display the errors for the user. You will find that User's do not like having to click OK on a MsgBox before they can fix an incorrect entry. However, if you don't like it then revert to the MsgBoxes.

    Workbook SAMPLE B (Valid Day Dates) Inserts a list of valid dates for the selected month. I have also added a year which defaults to current year. See the code for the range of year options in the list. From current year minus 1 to current year plus 1. (Year is required for Valid dates so that Feb has 29 days in leap year). 

    Both workbooks just copies the ActiveCell to the textbox on the userform.

    I have inserted comments to try to explain what the code does and also against some of your code that I don't understand but feel free to get back to me if anything you do not understand.


    Regards, OssieMac

    • Marked as answer by Lanmanna Monday, March 27, 2017 8:35 PM
    Tuesday, March 21, 2017 4:29 AM

All replies

  • Really require a copy of the workbook to follow what you are attempting to do. If you can upload the workbook to OneDrive and I will try to help.

    Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

    1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
    3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
    4. Go to this link.  https://onedrive.live.com
    5. Use the same login Id and Password that you use for this forum.
    6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
    7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    8. Right click the file on OneDrive and select Share.
    9. Select "Get a Link" from the popup menu.
    10. Click in the field displaying the link and it should highlight and then Copy and Paste the link into your reply on this forum. (I suggest that you avoid the "Copy" button on the "Get a link" screen because it introduces additional steps that are not required.)

    Regards, OssieMac

    Sunday, March 19, 2017 11:19 PM
    • Edited by Lanmanna Monday, March 20, 2017 1:12 PM
    Monday, March 20, 2017 1:12 PM
  • Try the example workbooks at the following link. (Two workbooks in the zipped file).

    https://1drv.ms/u/s!ArAXPS2RpafCjG5h0gJ61qOGSIsK

    Workbook SAMPLE_A is a simple code edit to your example. However, I have used loops to add the lists to the months and days and have changed the code to use a label to display the errors for the user. You will find that User's do not like having to click OK on a MsgBox before they can fix an incorrect entry. However, if you don't like it then revert to the MsgBoxes.

    Workbook SAMPLE B (Valid Day Dates) Inserts a list of valid dates for the selected month. I have also added a year which defaults to current year. See the code for the range of year options in the list. From current year minus 1 to current year plus 1. (Year is required for Valid dates so that Feb has 29 days in leap year). 

    Both workbooks just copies the ActiveCell to the textbox on the userform.

    I have inserted comments to try to explain what the code does and also against some of your code that I don't understand but feel free to get back to me if anything you do not understand.


    Regards, OssieMac

    • Marked as answer by Lanmanna Monday, March 27, 2017 8:35 PM
    Tuesday, March 21, 2017 4:29 AM
  • Thank you very much!
    Monday, March 27, 2017 8:35 PM