new to userforms..question about populating a lable box

[redspanna]

Hi all

assuming that

Sub Dispay_userform()
rows.Show
End sub

will display my rows userform
how, when it is displayed can I get the Lable4 box to be populated with the value in cell T2 in the data worksheet?

thanks

 

[al_b_cnu]

Hi,

Assuming your worksheet is named 'Data', try:

Private Sub UserForm_Initialize()
Label4.Caption = CStr(Sheets("Data").Range("T2").Value)
End Sub

 

[VoG]

Try like this (I wouldn't call your form rows - that is a VBA keyword)

Sub Dispay_userform()
With UserForm1
    .Label4.Caption = Sheets("Data").Range("T2").Value
    .Show
End With
End Sub

 

[redspanna]

Hi, yep thats works - thanks

I have a follow up question which is more tricky and well beyond my simple vba knowledge !

This code asks the user (via input box) to input the number of rows to be inserted....

Sub InsertRows2()
    Dim Rws As Long, LastRw As Long
    
    Do
        Rws = Application.InputBox( _
            Title:="Insert how many rows?", _
            Prompt:="Enter the number of rows to be inserted", _
            Type:=1)
    Loop While Rws < 0
    If Rws > 0 Then
        LastRw = Range("B" & rows.Count).End(xlUp).Row
        If LastRw < 7 Then
            MsgBox "Unable to add rows in correct location"
        Else
            rows(LastRw - 6).Resize(Rws).Insert
        End If
    End If
End Sub

when my userform (now called 'insertrows') is displayed stating that xx number of rows need to be added,once the user clicks on the yes command button can the code above be added to ur previous code to insert the number of rows stated..

Sub Dispay_userform()
With Insertrows
    .Label4.Caption = Sheets("Data").Range("T2").Value
    .Show
End With
End Sub

so above code will state the number of rows to be added is xx (xx being the value of cell T2 from data tab)
when user selects the 'yes' command button the code from the very top of this post runs to insert xx rows

hope that makes sense and many thanks

 

[VoG]

Maybe like this

Sub Dispay_userform()
With InsertRows
    .Label4.Caption = Sheets("Data").Range("T2").Value
    .Show
    ActiveCell.Resize(.Label4.Caption).EntireRow.Insert
End With
End Sub

 

[redspanna]

VoG

The xx rows are inserted at the very top of the sheet
as per the code to insert the given number of rows, these are at the bottom of the sheet column B with an ofset of 6 rows....this code works great

Sub InsertRows2()
    Dim Rws As Long, LastRw As Long
    
    Do
        Rws = Application.InputBox( _
            Title:="Insert how many rows?", _
            Prompt:="Enter the number of rows to be inserted", _
            Type:=1)
    Loop While Rws < 0
    If Rws > 0 Then
        LastRw = Range("B" & rows.Count).End(xlUp).Row
        If LastRw < 7 Then
            MsgBox "Unable to add rows in correct location"
        Else
            rows(LastRw - 6).Resize(Rws).Insert
        End If
    End If
End Sub

but instead of using the value input into the input box I want the value used from cell ref T2 in data worksheet, ie that shown in my userform (Lable4)



thanks again

 

[VoG]

Like this maybe

Sub Dispay_userform()
Dim Rws As Long, LastRw As Long
Rws = Sheets("Data").Range("T2").Value
With InsertRows
    .Label4.Caption = Rws
    .Show
End With
If Rws > 0 Then
    LastRw = Range("B" & Rows.Count).End(xlUp).Row
    If LastRw < 7 Then
        MsgBox "Unable to add rows in correct location"
    Else
        Rows(LastRw - 6).Resize(Rws).Insert
    End If
End If
End Sub

 

[redspanna]

almost.....

Just want the insert action linked to the 'Yes' command button on my userform, at the moment it iserts on closing the form by clicking on the X button

cheers

 

[VoG]

Maybe like this then

Private Sub CommandButton1_Click()
Dim Rws As Long, LastRw As Long
Rws = Sheets("Data").Range("T2").Value
With InsertRows
    .Label4.Caption = Rws
    .Show
End With
If Rws > 0 Then
    LastRw = Range("B" & Rows.Count).End(xlUp).Row
    If LastRw < 7 Then
        MsgBox "Unable to add rows in correct location"
    Else
        Rows(LastRw - 6).Resize(Rws).Insert
    End If
End If
End Sub

 

[al_b_cnu]

Hi,

wouldnt a simple MsgBox (instead of a userform) suit, e.g.

Sub xxx()
Dim Rws As Long, LastRw As Long

Rws = Sheets("Data").Range("T2").Value

If MsgBox(prompt:="do you want to insert " & Sheets("Data").Range("T2").Value & " rows?", _
          Buttons:=vbQuestion + vbYesNo) = vbYes Then
    If Rws > 0 Then
        LastRw = Range("B" & Rows.Count).End(xlUp).Row
        If LastRw < 7 Then
            MsgBox "Unable to add rows in correct location"
        Else
            Rows(LastRw - 6).Resize(Rws).Insert
        End If
    End If
End If
End Sub