Commandbutton --> Combobox value

[Zahhhaaaa]

Hello,

I have worksheet that contains CommandButton1 for "save" and ComboBox5, previously I've saved worksheet based on cell name, range e2:f2 with SaveAs-formula, like this;

I write my name on cell, zahhhaaaa, then press "save", and file will be saved as "zahhhaaaa.xlsomething".

What I'm trying, is to save worksheet based on ComboBox5 value, I've linked combobox to range e2:f2, but it seems not to work that easy :P

I choose name from combobox, and it appears on e2:f2, then I press "save", I'm getting an error; "Runtime error 1004", everything else is in finnish language so I'm not gonna waste mine or yours time to translate what does it say.. But I hope you'll get the point.

It gives an error, but it DOES save the file, with a name I've chosen from combobox (and what appeared on cell range e2:f2)


Cheers from Finland!

 

[AlphaFrog]

Try to save it using just Range("E2")

The Save doesn't like the multicell reference "E2:F2"

 

[Sektor]

Use:

    Range("E2").MergeArea.Cells(1, 1)

 

[Zahhhaaaa]

Thanks! It works just like I wanted!

 

[Zahhhaaaa]

Could you give me an advice to solve this problem too?

I got commandbutton to open new sheet, and below is the code I'm using to clear data from cells, so that the new sheet is clean from all text.

But it won't clear text from TextBoxes, there are six of them, TextBox1, TextBox2,....and TextBox6, what is the code for this? Added in code below

Sub NewSheet()
Dim CurrentDay As Integer, NewName As String
If IsNumeric(Right(ActiveSheet.Name, 2)) Then
CurrentDay = Right(ActiveSheet.Name, 2)
ElseIf IsNumeric(Right(ActiveSheet.Name, 1)) Then
CurrentDay = Right(ActiveSheet.Name, 1)
Else
Exit Sub
End If
CurrentDay = CurrentDay + 1
NewName = Format(Date, "dd.mm.yyyy")
Dim checkWs As Worksheet
On Error Resume Next
Set checkWs = Worksheets(NewName)
If checkWs Is Nothing Then
Worksheets(ActiveSheet.Name).Copy After:=Worksheets(ActiveSheet.Index)
With ActiveSheet
.Name = NewName
.Range("E2").ClearContents
Range("D2").ClearContents
End With
Else
Set checkWs = Nothing
MsgBox "Päivälle " & NewName & " on jo merkintä!"
End If
End Sub


Cheers again!

 

[AlphaFrog]

[COLOR="Red"]Dim oleObj As OLEObject[/COLOR]


With ActiveSheet
    .Name = NewName
    .Range("E2").ClearContents
    .Range("D2").ClearContents
[COLOR="Red"]    For Each oleObj In ActiveSheet.OLEObjects
       If oleObj.progID = "Forms.TextBox.1" Then oleObj.Object.Value = ""
    Next oleObj[/COLOR]
end with

Forum Tip: Pasting VBA code in the forum editor
It would be best if you surround your VBA code with code tags e.g [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier.
When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign #.

 

[Zahhhaaaa]

Ok thanks! I'm a new user here so I'll try to remember that next time

 

[Zahhhaaaa]

Hello again, I need some help again, I'm a rookie programming excel so I need some kick to get start.

But I gotta question;

In worksheet, i have four columns, A6, B6, C6, D6

A6 contains ComboBox4, named Groups
B6 contains ComboBox3, Work
C6 contains ComboBox7, Area
D6 contains two textboxes 7 and 8. named Working time

What I need to, is to add a commandbutton to copy this row, range A6:D6 and place it in range A7:D7.

I know that I can do this by recording, but when I do this, it records macro normally but ignores comboboxes etc. What I got is an empty row.

And also, I need to have many many rows. So is it possible to have Commandbutton "New row", and everytime I click this, it gives me a new row WITH combobox3 4 and 7, and textboxes7 and 8

Who gives me the answer, I salute you!!!