Vba to copy cell interior color

sebseb · Mar 6th 2021

Code

Private Sub CommandButton1_Click()
Dim Actsheet As String
Application.ScreenUpdating = False
On Error Resume Next
ActiveWorkbook.Sheets("Material_50").Visible = True
ActiveWorkbook.Sheets("Material_50").Copy _
after:=ActiveWorkbook.Sheets("ZALOGA")
ActNm = ActiveSheet.Name
'ActiveSheet.Name = InputBox("")
Sheets(ActiveSheet.Name).Visible = True
ActiveWorkbook.Sheets("Material_50").Visible = False
Application.ScreenUpdating = True
'Make sure the name is valid
On Error Resume Next
'Get the new name
'Keep asking for name if name is invalid
Do
userInput = InputBox("Vnesi ime MATERIALA novega lista.") ....... 
'Insert the name of the new sheet
If StrPtr(userInput) = 0 Then
MsgBox "Stisnil si CANCEL- pobriši zavihek sam!!!" ....... 
'push CANCEL - but you should delete the tab by yourself
Application.DisplayAlerts = False
Exit Sub
Else
If userInput = vbNullString Then
MsgBox "Ne moreš nadaljevati brez vnesenega imena za ZALOGO!" ..... 
You cannot continue without insert new name of sheet
Else
ActiveSheet.Name = userInput
'MsgBox userInput & " entered."
End If
End If
Loop Until userInput <> vbNullString

Do Until Err.Number = 0
Err.Clear
ActiveSheet.Name = InputBox("Poskusi ponovno!" _ ..... try again
& vbCrLf & "Ime lahko že obstaja!" _ ..... name already exsist
& vbCrLf & "Prosim vnesi ime MATERIALA NOVEGA LISTA!!!") .... please inesrt new name of heet
Loop
On Error GoTo 0

Display More

**Carim** · Mar 6th 2021

Thanks a lot ...!!!

This macro seems to work properly ... with no errors ...

So ...does it mean that you need a different procedure to Add a new sheet ... ?

sebseb · Mar 6th 2021

only what i need is when i push CANCEL or X then the process will deny or cencelbecause now i anyway sheet created and than i should delete sheet manual

sebseb · Mar 6th 2021

try and you will see what will happen

**Carim** · Mar 6th 2021

What you are saying is ...

If you select Cancel or Exit ... the New sheet is created anyhow ... right ...

But ... by the way ... are you the author of this macro ...?

sebseb · Mar 6th 2021

If you select Cancel or Exit ... the New sheet is created anyhow ... right ... YES

But ... by the way ... are you the author of this macro ...? ..... NO, is nor mine ... find on inetrnet (ni moja.. najdu sem jo na netu)

**Carim** · Mar 6th 2021

Quote from sebseb

If you select Cancel or Exit ... the New sheet is created anyhow ... right ... YES

But ... by the way ... are you the author of this macro ...? ..... NO, is nor mine ... find on internet (ni moja.. najdu sem jo na netu)

Well ... the Do Loop is not exactly where it should be ...

sebseb · Mar 6th 2021

no CODE is mine .... all find on inetrnet...

sebseb · Mar 6th 2021

Well ... the Do Loop is not exactly where it should be ...

and how is look like:)

sebseb · Mar 6th 2021

btw... i wish to learn programing

**Carim** · Mar 6th 2021

To expand a little ...

You hare copied the same procedure 4 times to add a new sheet ... one behind each Button (Buttons 1,3,4,5 )

whereas you could have one single global procedure in a standard module ... called by any number of command buttons ...

sebseb · Mar 6th 2021

ok:)

sebseb · Mar 6th 2021

carim.. where came you from??

sebseb · Mar 6th 2021

i have to go on work:(

see you

**Carim** · Mar 6th 2021

Hello again,

Below is a quick fix ... to be tested ...

Code

Private Sub CommandButton1_Click()
' Material_50  Red Button
Dim Actsheet As String, ActNm As String, userInput

' Make Worksheet Visible
ActiveWorkbook.Sheets("Material_50").Visible = True

' Start Loop
Do
'Make sure the name is valid
 On Error Resume Next
'Keep asking for Sheet Name if Name is invalid
        userInput = InputBox("Vnesi ime MATERIALA novega lista.")
        If StrPtr(userInput) = 0 Then
            ' User canceled ''''''''''''''''''''''''''''''''''
           Exit Sub
        Else
            ' User did not enter anything ''''''''''''''''''''
            If userInput = vbNullString Then
                MsgBox "Ne moreš nadaljevati brez vnesenega imena za ZALOGO!"
            Else
                ' Make Copy of Worksheet AFTER ZALOGA
                ActiveWorkbook.Sheets("Material_50").Copy After:=ActiveWorkbook.Sheets("ZALOGA")
                ActNm = ActiveSheet.Name
                Sheets(ActiveSheet.Name).Visible = True
                ActiveWorkbook.Sheets("Material_50").Visible = False
                Application.ScreenUpdating = True
                ActiveSheet.Name = userInput
            End If
        End If
    Loop Until userInput <> vbNullString
 
    Do Until Err.Number = 0
        Err.Clear
        ActiveSheet.Name = InputBox("Poskusi ponovno!" _
        & vbCrLf & "Ime lahko že obstaja!" _
        & vbCrLf & "Prosim vnesi ime MATERIALA NOVEGA LISTA!!!")
    Loop
    
 On Error GoTo 0
End Sub

Display More

Hope this will help

sebseb · Mar 7th 2021

Carim tnnnnnnnnnnnxxxxxxxxxxxxxxxxxx:))) 10000000000 x time

next time when you visit SLO please contact me... i will pay you a coffee

here is overall tabel, if somebody need

sebseb · Mar 7th 2021

please conntact me PM

**Carim** · Mar 7th 2021

Extremely happy to hear this could help you ...

Vba to copy cell interior color

Participate now!

Share