Pre Typed Text Box Appear After Macro is Executed

[nirvehex]

Hi,

I have a navigation bar on one of my Excel tabs. It has a bunch of buttons on it which run various macros.

What I'm looking to do is once one of the buttons is clicked, say for example the "Scope of Work" button is clicked. This would run the "ScopeOfWorkInstructions" macro. This macro would make a text box to appear on the same tab just to the right of the button with pre typed text in it, auto expanding to how much text is in it. I also want a small "x" or "close" button in the corner of the text box. Is this possible to do with some VBA?

Thanks,

Mark

[raj85]

Hello Nirvehex,

Try below code change sheet name and range address as required.


Sub DynamicVBCOM()

Set oRng = Sheet1.Range("C2")
Sheet1.Activate
'Code to Create a button
Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=oRng.Left, Top:=oRng.Top, Width:=oRng.Width, Height:=oRng.Height)
Set oRng = Sheet1.Range("B2")
Set TextObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", _
Link:=False, DisplayAsIcon:=False, Left:=oRng.Left, Top:=oRng.Top, Width:=oRng.Width, Height:=oRng.Height)
ShtName = ActiveSheet.Name


Obj.Name = "Mybutton1"
ActiveSheet.OLEObjects(Obj.Name).Object.Caption = "X"
ActiveSheet.OLEObjects(TextObj.Name).Object.Text = "Message"
TextObj.Name = "MyTextBox1"


'Code to delete Text box text and code
Code = "Private Sub Mybutton1_Click()" & vbCrLf
Code = Code & "Call DeleteControl(ActiveSheet.Name)" & vbCrLf
Code = Code & "End Sub" & vbCrLf & vbCrLf
Code = Code & "Sub DeleteControl (Byval SheetName) " & vbCrLf
Code = Code & "Worksheets(SheetName).OLEObjects(""MyTextBox1"").Delete" & vbCrLf
Code = Code & "Worksheets(SheetName).OLEObjects(""Mybutton1"").Delete" & vbCrLf
Code = Code & "With ActiveWorkbook.VBProject.VBComponents(Worksheets(SheetName).CodeName).CodeM odule" & vbCrLf
Code = Code & ".Deletelines 1, .CountOfLines " & vbCrLf
Code = Code & "End With" & vbCrLf
Code = Code & "End Sub" & vbCrLf


'add macro at the end of the activesheet module
With ActiveWorkbook.VBProject.VBComponents(Worksheets(ShtName).CodeName).CodeMod ule
.insertlines .CountOfLines + 1, Code
End With


End Sub


Let me know in case of query

[Aussiebear]

@raj85, I'm easily lost, but what does the Code =Code & do? Its almost as if you are trying to nest the code somehow?

[Kenneth Hobs]

Code=Code is simply concatenating the previous Code string.

[nirvehex]

Hello Nirvehex,

Try below code change sheet name and range address as required.


Sub DynamicVBCOM()

Set oRng = Sheet1.Range("C2")
Sheet1.Activate
'Code to Create a button
Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=oRng.Left, Top:=oRng.Top, Width:=oRng.Width, Height:=oRng.Height)
Set oRng = Sheet1.Range("B2")
Set TextObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", _
Link:=False, DisplayAsIcon:=False, Left:=oRng.Left, Top:=oRng.Top, Width:=oRng.Width, Height:=oRng.Height)
ShtName = ActiveSheet.Name


Obj.Name = "Mybutton1"
ActiveSheet.OLEObjects(Obj.Name).Object.Caption = "X"
ActiveSheet.OLEObjects(TextObj.Name).Object.Text = "Message"
TextObj.Name = "MyTextBox1"


'Code to delete Text box text and code
Code = "Private Sub Mybutton1_Click()" & vbCrLf
Code = Code & "Call DeleteControl(ActiveSheet.Name)" & vbCrLf
Code = Code & "End Sub" & vbCrLf & vbCrLf
Code = Code & "Sub DeleteControl (Byval SheetName) " & vbCrLf
Code = Code & "Worksheets(SheetName).OLEObjects(""MyTextBox1"").Delete" & vbCrLf
Code = Code & "Worksheets(SheetName).OLEObjects(""Mybutton1"").Delete" & vbCrLf
Code = Code & "With ActiveWorkbook.VBProject.VBComponents(Worksheets(SheetName).CodeName).CodeM odule" & vbCrLf
Code = Code & ".Deletelines 1, .CountOfLines " & vbCrLf
Code = Code & "End With" & vbCrLf
Code = Code & "End Sub" & vbCrLf


'add macro at the end of the activesheet module
With ActiveWorkbook.VBProject.VBComponents(Worksheets(ShtName).CodeName).CodeMod ule
.insertlines .CountOfLines + 1, Code
End With


End Sub


Let me know in case of query

Thanks Raj! Where exactly do I insert my pre-written text that goes in the text box in the code above?

Thanks.

[Aussiebear]

Why do we need to concatenate the code? Why not simply write is as



'Code to delete Text box text and code
Private Sub Mybutton1_Click()
Call DeleteControl(ActiveSheet.Name)
End Sub

Sub DeleteControl (Byval SheetName)
Worksheets(SheetName).OLEObjects(""MyTextBox1"").Delete
Worksheets(SheetName).OLEObjects(""Mybutton1"").Delete
With ActiveWorkbook.VBProject.VBComponents(Worksheets(SheetName).CodeName).CodeM odule
.Deletelines 1, .CountOfLines
End With
End Sub

And does it serve any purpose being the middle of another code module?

[nirvehex]

Why do we need to concatenate the code? Why not simply write is as



'Code to delete Text box text and code
Private Sub Mybutton1_Click()
Call DeleteControl(ActiveSheet.Name)
End Sub

Sub DeleteControl (Byval SheetName)
Worksheets(SheetName).OLEObjects(""MyTextBox1"").Delete
Worksheets(SheetName).OLEObjects(""Mybutton1"").Delete
With ActiveWorkbook.VBProject.VBComponents(Worksheets(SheetName).CodeName).CodeM odule
.Deletelines 1, .CountOfLines
End With
End Sub

And does it serve any purpose being the middle of another code module?

But where in this code do I actually write what I want displayed in the text box? The idea behind the text box is that it will display directions.

[mikerickson]

Rather than writing code to the sheet's module, which would run into problems if the permissions weren't right, I might be easier to assign the new text box to a custom class which will have the predetermined code in it.

Alternatly, instead of a separate textbox for each instance, you could show different instances of a userform modelessly.

[nirvehex]

Ok I'm still very confused. All I'm looking for is code that pops up a text box with pre written text in it. The macro, or code, is activated by a button that I would assign the macro to. The text written in the text box would be pre typed somewhere within the code. I was also hoping the text box would have an X in the corner to close it when the user is done reading it.

So to summarize: User clicks button, text box pops up with pre-written instructions, user closes text box using small X in top right corner.

Thanks for any help you can provide!

[mikerickson]

If you could attach a workbook with the navigation bar, we could see what it is that you are after.

[nirvehex]

If you could attach a workbook with the navigation bar, we could see what it is that you are after.

Before I do let me try to explain one more time. Let me know if I should attach after this explanation.

1. I have a button a worksheet.

2. When this button is clicked it activates the macro.

3. The macro makes a text box appear with pre written text in it, which I could put in quotes somewhere within the VBA code.

4. There is an small X in the top right corner of the text box that the user can click to make the text box go away.

That's all I'm after :)

[GTO]

Before I do let me try to explain one more time. Let me know if I should attach after this explanation.

1. I have a button a worksheet.

2. When this button is clicked it activates the macro.

3. The macro makes a text box appear with pre written text in it, which I could put in quotes somewhere within the VBA code.

4. There is an small X in the top right corner of the text box that the user can click to make the text box go away.

That's all I'm after :)



Presumably you mean you already have a button on a worksheet? A forms button or an activex button?
Activates a macro that is already written? Or, you want suggestions on writing the macro?
From your initial post, I thought you wanted the code to create a textbox. Do you already have a textbox on the sheet and the visible property is set to False?
TextBoxes do not have a close button.


I would suggest that you do include an attached workbook that would show us where you are at exactly.

Mark

[lucas]

Does it have to be a textbox? You would need to put that on a userform and call the userform from the button. Just use a label on a userform if that is what you are trying to do.

[Tommy]

message box


Private Sub ShowMessage(Message As String) MsgBox Message
End Sub


Private Sub commandbutton1_click()
Dim Note As String
Note = " Hi How are ys?"
ShowMessage Note
End Sub

[nirvehex]

Tommy, I think this is going to work, just a question..

How would I write the code as a regular macro, instead of doing the private sub?



Sub ScopeOfWorkInsturctions()
MsgBox Message
End Sub

[nirvehex]

Nevermind. I got it.



Sub ScopeofWorkInstructions()


MsgBox " SCOPE OF WORK INSTRUCTIONS:" _
End Sub