TextBox value as Formula Overwrites

[SamDsouza]

Hello

The below code somehow overwrites the formula typed in rangeE1
What i am trying to do is that if i check value as formula then the txtValue should display the Formula if unchecked then it should display the value in textbox
this is like Formula Bar and Cell Value.
Second thing if i type as value then it should display value both in Formual bar and Range E1 ie when checkbox is unchecked

Overwriting should only if i completely make the txtValue Empty or blank and with new values typed in it

SamD

Private Sub txtValues_AfterUpdate()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
ws.Activate


If CheckBoxEnterFormula.Value = True Then
        ws.Range("E1").Formula = txtValues.Value
   Else
        ws.Range("E1").Value = txtValues.Value
End If
End Sub

Private Sub CheckBoxEnterFormula_Click()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
ws.Activate

If CheckBoxEnterFormula.Value = True Then
      ws.Range("E1").Formula = txtValues.Value
  Else
     txtValues.Value = ws.Range("E1").Value
End If

End Sub

59

 

[NimishK]

SamD

Request you to study on .HasFromula property of Range Object, Nevertheless i've made some color combination for differentiating whether one Entering As Values or as Formula
This code will give you some idea.

Private Sub UserForm_Initialize()
Set ws = Worksheets("Sheet1")
ws.Activate
txtValues.BackColor = RGB(255, 204, 204) 'pink color

If Not ws.Range("E1").HasFormula Then
       txtValues.BackColor = RGB(255, 204, 204)
       txtValues.Text = ws.Range("E1").Value
       CheckBox1.Caption = "Cell E1 contains Value"
ElseIf ws.Range("E1").HasFormula Then
       CheckBox1.Value = True
       CheckBox1.Caption = "Cell E1 contains Formula"
       txtValues.BackColor = RGB(255, 255, 255) 'White
       txtValues.Text = ws.Range("E1").Formula
End If
End Sub

Private Sub CheckBox1_Click()
Set ws = Worksheets("Sheet1")
ws.Activate
If CheckBox1.Value = True And ws.Range("E1").HasFormula Then
   txtValues.BackColor = RGB(255, 255, 255) 'white
   txtValues.Text = ws.Range("E1").Formula
   CheckBox1.Caption = "Cell E1 contains Formula"
  ElseIf CheckBox1.Value = False And ws.Range("E1").HasFormula Then
   txtValues.BackColor = RGB(255, 204, 204) 
   txtValues.Text = ws.Range("E1").Value
   CheckBox1.Caption = "Cell E1 contains Value"
End If
End Sub

Private Sub txtValues_AfterUpdate()
If txtValues.Text = "" And txtValues.Value = "0" Then
   ws.Range("E1").Value = ""   
  Else
    ws.Range("E1").Value = txtValues.Value
 If ws.Range("E1").HasFormula Then
    CheckBox1.Caption = "Cell E1 contains Formula"
  Else
    CheckBox1.Caption = "Cell E1 contains Value"
End If
End If
End Sub

NimishK

 

[SamDsouza]

Thanks NimishK you made me move on.

Yes i had to modify TxtValues_Afterupdate event to suit the desired results but still not upto the mark.
Because as per your coding there is miss out on updating txtValues either with formula and or only values. if Success then will let you know

Thanks and regards
SamD