Inserting text using VBA

[WuAhUuU]

I am creating a macro where depending on the day of the year, it will either be OPEN or CLOSE...

However, I would like to know how can the words OPEN or CLOSE be inserted by the macro.

Example of the code:

Private Sub cmdcancel_Click()
    Unload Me
End Sub
 
Private Sub cmdinsert_Click()
Set portugal = Worksheets("PORTUGAL")
Set priv= Worksheets("PRIVATE")
Set param= Worksheets("PARAMETERS")
 
Application.ScreenUpdating = False
 
If txtdate = "" Then
msg = "Please be sure all fields are complete"
MsgBox msg
GoTo Finish
End If
 
i = 0
Do
i = i + 1
Loop Until priv.Cells(i + 2, 1) = ""
 
If optopen = True Then word = OPEN      'NEED HELP HERE... These are option buttons
If optclose = True Then word = CLOSE     'NEED HELP HERE... These are option buttons
 
priv.Cells(i + 2, 1) = txtdate
priv.Cells(i + 2, 2) = "=WORKDAY(A" & (i + 2) & ",3,PARAMETERS!$D$3:$D$65536)"
priv.Cells(i + 2, 3) = word
 
Unload Me
 
Finish:
End Sub
 
 
Private Sub UserForm_Initialize()
Set portugal = Worksheets("PORTUGAL")
Set priv= Worksheets("PRIVATE")
Set param= Worksheets("PARAMETERS")
 
datetoday = Format(DateTime.Now, "mm/dd/yyyy")
txttradedate = datetoday
 
End Sub

 

[Mike Blackman]

Hi,

Change your lines to;

If optopen = True Then word = "OPEN"

 

[BatleyBoy]

hi there,

where do you want to insert text? A cell, a textbox; the method varies....

 

[WuAhUuU]

the words have to be pasted into cells after the insert button is pressed... something to followup. I need this text because of the following line:

priv.Cells(i + 2, 11) = "=IF(C" & (i + 2) & "=[COLOR=red][B]OPEN[/B][/COLOR],SUM(I" & (i + 2) & ":J" & (i + 2) & "),I" & (i + 2) & "-J" & (i + 2) & ")"

The following code should be when inserted into a cells after pressing the Insert button. The above has to be exactly as the below:

=IF(C2="OPEN",SUM(I2:J2),I2-J2)

 

[Mike Blackman]

Hi,

You will need to add the quotations into the VBA also, not sure on the syntax so try these, one should work;

priv.Cells(i + 2, 11) = "=IF(C" & (i + 2) & "="OPEN",SUM(I" & (

Or

priv.Cells(i + 2, 11) = "=IF(C" & (i + 2) & "=""OPEN"",SUM(I" & (