A shape used for calling a userform

jdcar

Board Regular
Joined
Apr 27, 2012
Messages
160
Hi
I have a project that is based on the data in spreadsheet1.
If I insert a "shape" in another spreadsheet 4, such as a command button to call the user form, it appears but the instructions given to execute, it don't do it.
If I insert a shape, such as a command in worksheet1, it works.
Is there a specific reason or code that is not correct for this situation to occur?
Coud you please help me?
Thank you
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

jdcar

Board Regular
Joined
Apr 27, 2012
Messages
160
Hi Daves
VBA Code:
‘ * codes of the commands that do not obey, if I choose to use shape on sheet4 to open the form.
Private Sub CommandButton1_Click()    '  *RE_EDIT
Dim i As Integer
       UserForm2.lblIDOrder.Caption = ListBox1.List(ListBox1.ListIndex)
       UserForm2.TextBox5.Text = ListBox1.Column(1, ListBox1.ListIndex)
       UserForm2.TextBox4.Text = ListBox1.Column(3, ListBox1.ListIndex)
       UserForm2.TextBox1.Text = ListBox1.Column(4, ListBox1.ListIndex)
       UserForm2.TextBox2.Value = ListBox1.Column(5, ListBox1.ListIndex)
       UserForm2.TextBox3.Text = ListBox1.Column(6, ListBox1.ListIndex)
UserForm2.Show
   For i = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(i) Then Me.ListBox1.Selected(i) = False
   Next i
   ListBox2.Value = ""
End Sub

Private Sub CommandButton2_Click()     ' *DELETE
Dim i As Integer
If ListBox1.Value = "" Then
        MsgBox ("Please fill up, the Months or Value commands, and select them")
      Else
       If MsgBox("Are you sure you want to delete this row?", vbYesNo + vbQuestion, "Delete row") = vbYes Then
              For i = 1 To Range("a10000").End(xlUp).Row
                If Cells(i, 1) = ListBox1.List(ListBox1.ListIndex) Then
                  Rows(i).Select
                  Selection.Delete
                End If
              Next i
       End If
End If
With ListBox3
  .Clear
  .AddItem
     For i = 0 To 6
       .List(.ListCount - 1, i) = ListBox1.List(ListBox1.ListIndex, i)
     Next i
  Label5.Caption = " Value was Delete"
End With
   ComboBox1.Value = ""
   ListBox1.RowSource = ""
   ListBox2.Clear
End Sub











Private Sub UserForm_Initialize()
On Error Resume Next
   Dim Base As Range
   Dim Nome As String
   Dim Lh As Long
   Dim dict, key
   Dim lastrow As Long
  
  
 lastrow = Application.WorksheetFunction.CountA(Range("C:C"))
Sheets("sheet1").Range ("c2:c" & lastrow)
     dict = Sheets("sheet1").Value
With CreateObject("scripting.dictionary")
  .comparemode = 1 'vbtextcompare - case words doesn't matter:apple
     For Each key In dict
       If Not .exists(key) Then .Add key, Nothing
     Next
       If .Count Then Me.ComboBox1.List = Application.Transpose(.keys)
End With
Sheets("sheet1").Range ("h2:h" & lastrow)
   dict = Sheets("sheet1").Value
With CreateObject("scripting.dictionary")
   .comparemode = 1
      For Each key In dict
        If Not .exists(key) Then .Add key, Nothing
      Next
        If .Count Then Me.ComboBox2.List = Application.Transpose(.keys)
End With
    
      Me.ComboBox1.List = Array("JANEIRO", "FEVEREIRO", _
         "MARÇO", "ABRIL", "MAIO", "JUNHO", "JULHO", "AGOSTO", _
         "SETEMBRO", "OUTUBRO", "NOVEMBRO", "DEZEMBRO")

      Me.ComboBox2.List = Array("SAUDE", _
       "TRANSPORTE", "ALIMENTAÇÃO", "DESP.FIXAS", "IMPOSTOS", "DESP.OCASION", "AAA", "BBB", "CCC")
Call TrueFalseBox 'Modulo3 situação inicial
Lh = Sheet1.Range("A1").CurrentRegion.Rows.Count
Set Base = Sheet1.Range(Sheet1.Cells(2, 1), Sheet1.Cells(Lh, 7)) 'Sheet1.Range("A1").CurrentRegion
Nome = "'" & Sheet1.Name & "'!"
ListBox1.ColumnCount = 7
Sheet2.Range("l2:m2").ClearContents
End Sub









Option Explicit
'userform2
Private Sub CommandButton1_Click() 'Save
Dim I As Integer
Application.ScreenUpdating = False
On Error Resume Next
'Record in listbox1
With userform1.ListBox1
  For i = 2 To Range("g10000").End(xlUp).Row
     If Cells(i, 1) = lblIDOrder.Caption Then
        Cells(i, 2) = TextBox5.Text
        Cells(i, 4) = TextBox4.Text
        Cells(i, 5) = TextBox1.Text 
        Cells(i, 6) = TextBox2.Value
        Cells(i, 7) = TextBox3.Text   
     End If
  Next i
End With
With userform1
    .CheckBox1.Value = False
    .TextBox1.Value = ""
    .ComboBox1.Value = ""
    .ListBox1.RowSource = ""
    .Label5.Caption = "Rectified Value"
    .ListBox3.RowSource = "sheet2!t2:z2"
End With
‘Record in worksheet
Sheets("Sheet2").Range("T2").Value = lblIDOrder.Caption
Sheets("Sheet2").Range("U2").Value = TextBox5.Value
Sheets("Sheet2").Range("V2").Value = lblMonth.Caption
Sheets("Sheet2").Range("W2").Value = TextBox4.Value
Sheets("Sheet2").Range("X2").Value = TextBox1.Value
Sheets("Sheet2").Range("Y2").Value = TextBox2.Value
Sheets("Sheet2").Range("Z2").Value = TextBox3.Value
'Sheets("Sheet1").Select
Unload Me
'UserForm1.ListBox2.Clear
userform1.ListBox3.Value = ""
userform1.ListBox5.Value = ""
Application.ScreenUpdating = True
End Sub
Private Sub ListBox1_Change()
Dim cell As Range
'On Error Resume Next
'Load to ListBox
  For Each cell In Worksheets("Sheet2").Range("t2:z2")
    userform1.ListBox1.AddItem cell.Value
  Next cell
End Sub
Private Sub UserForm_Initialize()
lblMonth.Caption = userform1.ComboBox1.Value
Sheet2.Range("T2:Z2").ClearContents
End Sub

MODULE
Option Explicit
============
Sub FiltroCbo()
Dim Base As Range
Dim Crt As Range
Dim Filtrada As Range
Dim Nome As String
Dim Lh As Long

Set Base = Sheet1.Range("A1").CurrentRegion
Set Crt = Sheet2.Range("L1:L2")

Base.AdvancedFilter xlFilterCopy, Crt, Sheet2.Range("A1:j1")
Lh = Sheet2.Range("A1").CurrentRegion.Rows.Count

Set Filtrada = Sheet2.Range(Sheet2.Cells(2, 1), Sheet2.Cells(Lh, 7))

Nome = "'" & Sheet2.Name & "'!"
userform1.ListBox1.RowSource = Nome & Filtrada.Address
userform1.ListBox1.ColumnHeads = True

If Lh = 1 Then
userform1.ListBox1.ColumnHeads = False
End If

End Sub
excel
Thanks for the quick response. I hope that the codes I attach are enough to be able to reach some conclusion of the reason that led me to ask for help:
 

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,087
You need to specify the sheet you are counting rows for.
lastrow = Application.WorksheetFunction.CountA(Range("C:C"))
Sheets("sheet1").Range ("c2:c" & lastrow)

Lastrow would be for the active sheet because you did not indicate what sheet you are counting the rows
 

jdcar

Board Regular
Joined
Apr 27, 2012
Messages
160
Hi Dave
I had to be absent, which is why I have not yet said anything to the exposed observation and I am grateful.
In this case I don't know how to specify the sheet that be counting the rows.
I'm new in vba and withdraw code here and there and I compose within the necessary harmony as possible.
Please help me how to specify the spreadsheet in Initialize or if it is necessary to make the specification elsewhere? how to do that?
Sheet4 is where I have the shape to call the userform, and sheet1 is where I have the data.
Sorry and thank you very much.
Jdcar
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top