VBA reset data in selected cells + xml table save button

[bglumac]

Hi guys,

I'm a noob in this area but I'm trying to make my daily job easier...
To do so, I created an excel order page with some VLOOKUP and data validation dependent dropdown lists.
I have a mess with internal codes and linking between codes so the purpose of this tool is to always return the correct code and export it to an .xml file for upload in SAP CRM.
Also I have two buttons for data reset in dropdown lists.
All is working nice but two things not working and one partially working.

First is the VBA for reseting second, dependent dropdown list every thime the first one is changed.
I tried with LOOP function but couldn't make it work, so I did it in a Noob's way, just copying the one cell code 54 times
It look's like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("F5").Address Then
    Range("G5").Value = "-"
End If
    If Target.Address = Range("F6").Address Then
    Range("G6").Value = "-"
End If
    If Target.Address = Range("F7").Address Then
    Range("G7").Value = "-"

Problems start when I added Combo box double click VBA in it...
First dropdown list is working, but second, dependent list is not (second one is working with IF and VLOOKUP to find specific code in named range).

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
  
On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    Cancel = True
    Application.EnableEvents = False
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 1
      .Height = Target.Height + 1
      .ListFillRange = str
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    Me.TempCombo.DropDown
  End If
  
errHandler:
  Application.EnableEvents = True
  Exit Sub
End Sub

Private Sub TempCombo_LostFocus()
  With Me.TempCombo
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End Sub

Another problem is that two reset buttons are working, but the third is not.
So first is reseting header to "-", second is reseting the named range, the entire table, both with fixed ranges:

Private Sub Table_click()
    Dim cl As Range
    For Each cl In Range("reset_data")
        cl = "-"
    Next cl
End Sub

I need thirdbutton to reset what is selected in that time, so If I need to reset rows 5,6,7 I want to add a VBA to the button so when I select F6:L12 and click reset, only selected cells go to "-"
Can someone please suggest a code for it as I tried with "Set rng = Selection" and "Set tng = Application.Selection" but with no luck...

The third issue is xml export...
I tried to copy and combine some example code but with no luck.
It creates .xml but not for selection or fixed range (never mind) but the data is wrong as I don't know how...


What I need is a creation of .xml file with two columns of data from selected cells or entire table (fixed, never mind which one is it) after clicking the button.


If you guys can help, It would be a life saver
Thank you all in advance !!!

 

[AlphaFrog]

It would be best if you put each problem in its own thread as they are each unique questions.

Here's code for two problems.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=green]'Reset dependent dropdown when Parent dropdown changes[/COLOR]
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(Range("F5:F59"), Target) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        Intersect(Range("F5:F59"), Target).Offset(, 1).Value = "-"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()
    [COLOR=green]'thirdbutton to reset what is selected in that time[/COLOR]
    Selection.Value = "-"
End [COLOR=darkblue]Sub[/COLOR]

I don't know the answers to your other issues.

 

[bglumac]

@AlphaFrog THX for the help
First one is working like a charm and the highlight is tha much faster.

Problem with the second one is similar I had before, "type mismach".

Private Sub CommandButton1_Click()
'thirdbutton to reset what is selected in that time
Selection.Value = "-"
End Sub[/CODE]

I don't know the answers to your other issues.

Exactly the same I got with "Set rng = Selection"


Maybe you know what's wrong ?
THX bro !!!

 

[AlphaFrog]

It works for me. I don't know what wrong. Are you selecting something that is not a cell range?

 

[bglumac]

I think not.
But what range, I didn't set any range for it know, as range for reseting first dropdown list ended with that dub.
I corrected the range to F5:F54 but it's for last sub.

It works for me. I don't know what wrong. Are you selecting something that is not a cell range?

 

[AlphaFrog]

You select a range with the mouse and click the Command button. It then is suppose to set all the cells the selected range to equal "-"

 

[bglumac]

Yes, but error after click

 

[AlphaFrog]

Yes, but error after click

I don't know why it errors.

 

[bglumac]

THX for the first one

 

[bglumac]

The problem with reseting selected cells is SOLVED with this code:

Private Sub Selection_Click()
    ActiveCell.Value = "-"
End Sub

Xml export and trouble with Combo over data validation drop down list still not solved