Solved: Keep cell in edit mode after VBA paste

[jproffer]

The title might be kind of vague, but I'll try to clarify.

I have made a userform with a dropdown of all UDF's in an add-in I'm working on. Upon selection, the labels within the UF will have the description, syntax, examples, etc....but I also wanted to have a command button that will insert the beginning of the function into the active cell (similar to the native function wizard). For instance I would like to enter "=UDF(" stopping at the first "(".

OK, I can do all that by copying the opening of the formula from a worksheet in the add-in workbook and pasting it into the active cell.

The only thing I can't seem to do is keep the cursor in the cell (preferably at the far right of the partial formula that was just entered). IOW, I would like to keep the cell in, or put the cell back into, edit mode.

Any ideas??

Thanks in advance for any and all suggestions, workarounds, comments, thoughts, etc.

[mbarron]

If the sheet is the active window, you could use SendKey. Something like this:

Sub SendKeyExample()

Range("a1").Select
SendKeys "=UDF( ", True

End Sub

[jproffer]

First and foremost, thank you for the response.

I tried this:


SendKeys ins, True

and this


SendKeys (ins), True

where "ins" is a variable.

and neither worked. I'm assuming it's because I'm trying to use a variable. The partial formula is being pulled from a worksheet within the add-in, and will change as the selected function changes so hard coding is impossible.

[mbarron]

Your assumption is correct. SendKeys is equivalent to typing and cannot use variable input.

[jproffer]

I was afraid of that. At least now I'll get off of that train of thought. I had thought about sendkeys, but it hadn't worked...thought I was missing something in the syntax for using the variable.

So, any other thoughts on how to accomplish this? The main goal being to stop vba from exiting the cell after I paste (or insert the value in whatever way) from the other sheet.

[GTO]

Greetings,

Have you tried putting in the initial data first, then entering editmode?

Sub exa()
With Range("A2")
.Value = "Some text"
.Select
End With
Application.SendKeys "{F2}"
End Sub


Hope that helps,

Mark

[jproffer]

Thanks GTO, but that didn't work for some reason. This is what I have now.



Private Sub CommandButton1_Click()
ThisWorkbook.Sheets("UDFs").Range("A24").Value = ComboBox1.Value
ins = ThisWorkbook.Sheets("UDFs").Range("B32").Value
ActiveCell.Value = ins
Application.SendKeys "{F2}"
End Sub

where ThisWorkbook is the addin workbook, but the active cell is in another. It bugs out on the activecell.value=ins line.

Also tried:



Private Sub CommandButton1_Click()
ThisWorkbook.Sheets("UDFs").Range("A24").Value = ComboBox1.Value
ThisWorkbook.Sheets("UDFs").Range("B32").Copy
ActiveCell.PasteSpecial xlPasteValues
Application.SendKeys "{F2}"
End Sub

and it runs but the F2 doesn't put the cell into edit mode as it should.

Combobox1 holds the name of the UDF they want to use. That is pasted into the worksheet, which does the vlookup for all the different things I'm using...the last of which is the partial formula we're talking about here. That is where the ranges come in to play. All of that works. When I tried the variable code (top version) the variable was set correctly but errored out on trying to make the activecell equal that value.

I'm out of ideas...any thoughts are appreciated.

[lucas]

maybe you need to qualify the activecell as being in the activeworkbook.

[lucas]

Another mystery to me is why are you trying to put the cell in edit mode. If a userform is running you can do everything from there, even asking for input and then addint and concatenating it and then adding it to the sheet.....

I admittedly came in late but a few questions came up as I read the thread....

[jproffer]

Hi Lucas and thanks for the input.

Forgot to mention that. I tried "ActiveWorkbook.ActiveSheet.ActiveCell" also.

I suppose I could add another userform to construct the formula after the user makes their choice....hmmmmm. Actually, I kind of like that idea...makes it even more user friendly I guess.

I'm sure that will bring about more questions but one that I can already think of is:

What is the input box in the native function UF called? Or is there such a thing that we can add to our userforms? The box that you can click on the right side of it, and it sort of shrinks and goes to the sheet and a user can click on the cell they want to include in the function. Then it returns to the userform with that cell's address showing (and value showing to the right)......ahhhh, if I knew what to call it this would be easier, lol.

[lucas]

Set rng = Application.InputBox("Select the target range with the mouse", Type:=8)
If rng Is Nothing Then Exit Sub

[jproffer]

Yea I could use an inputbox for this but I think I found what I was talking about by just messing around, mostly by luck.

A refEdit box, that's basically whats in excel's native function "builder" or whatever you want to call it. I had seen it in the toolbox, but never really knew what it did.

I'll see how far I can take it from here, but I'm sure something will come up that I'll need help with.

Thanks to everyone for all the help.

[mikerickson]

It sounds like your userform is doing something very similar to what the native Insert Function feature already does with UDF's.

[jproffer]

It does...or I guess I should say, I HOPE it will when I'm done, lol.

But the native UDF menu is soooooooooo full, at least on my home PC, and the one at work (where this will be used) has most of the add-ins that I have (ASAP, Easy XL, etc.). I'm hoping this will be a little easier to find if they choose to use them as it will be a menu item on this particular ribbon control and it will hold only the functions from this add-in.

[mbarron]

I noticed that you had an = in the front of your cell entry in your original post. Which is why, I think, GTO's suggestion didn't work. Try this technique. It makes the entery a text statement, then edits the cell, strips off the apostrophe and repositions the cursor at the end of the cell again.

Sub test()
Dim strTest As String
strTest = "This is a test "
Selection = "'=" & strTest
SendKeys "{f2}", True
SendKeys "{home}", True
SendKeys "{delete}", True
SendKeys "{end}", True

End Sub

[jproffer]

Thanks mbarron,

That works if you run it from the workbook you want to use it on, but when I run it from the add-in, or more specifically from the userform it doesn't work.

It runs without bugging out, but from the F2 line and on, it does nothing. It won't go into edit mode, wont remove the " ' " and wont go to the end (naturally, since it's not in edit mode anyway).

I'm about to give up and move forward on the "wizard" userform to go with this one.

But, I do appreciate any ideas. If I can get this working I may give the user both options...insert into the cell and enter the arguments on your own, or use the wizard.

[mbarron]

My hay stack is about gone, but I'm going to grasp one more straw...

What if you hide the form before selecting the cells and then show the form again after?

[lucas]

Is the userform modal?

[jproffer]

Hiding the userform did the trick (thank goodness...believe me I was as "done with this" as you guys were). Set the variable, hide the form, sendkeys and unload the form from the hidden state.

Works great. Thank you guys so much.

Lucas, for the record the UF was modal and when I changed it to false, the cell on the sheet still wouldn't stay in the active state.